Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

Сегодня поговорим о функциях для поиска и замены части текстовой строки с помощью функций рабочего листа Excel.

Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК функция НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.

Синтаксис

НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)

ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

Искомый_текст   — это искомый текст.

Просматриваемый_текст   — это текст, включающий искомый текст. Для функции ПОИСК можно использовать знаки шаблона: знак вопроса (?) и звездочка (*). Знак вопроса соответствует любому знаку; звездочка соответствует любой последовательности знаков. Если нужно найти один из этих знаков, то следует поставить перед ними знак тильда (~).

Нач_позиция   — это позиция знака, с которой следует начинать поиск. Первый знак в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.

Функции НАЙТИ и ПОИСК можно использовать, например, в качестве аргументов функций ЛЕВСИМВ и ПРАВСИМВ.

Пример:

В столбце А мы имеем перечень сотрудников. Каждая запись включает Фамилию, имя и отчество, разделенные пробелами. Необходимо получить в отдельном столбце имена сотрудников. 

Использование мастера разделения текста по столбцам не всегда удобно, иногда нужно получить ответ именно с помощью функций.

Для выделения имени необходимо с помощью функции НАЙТИ определить порядковый номер первого пробела в тексте, затем номер второго пробела и выделить подстроку между пробелами.

Для обработки текста в ячейке А2 формула будет выглядеть так:

=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-НАЙТИ(" ";A2;1)-1)

Не пугайтесь. Функция НАЙТИ использована здесь 4 раза для вычисления местоположения нужногой подстроки. Первый пробел в ячейке А2 имеет порядковый номер НАЙТИ(" ";A2;1). Следовательно имя начинается с символа номер НАЙТИ(" ";A2;1)+1.

Второй пробел идет сразу за именем. Чтобы найти его, нужно искать пробел не с начала строки, а начиная с символа, следующего за первым пробелом. Получается конструкция НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1). Если из полученного числа вычесть место первого символа имени, мы получим длину имени.

{mospagebreak}

Иногда могут оказаться полезными функции ЗАМЕНИТЬ и ПОДСТАВИТЬ.

Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ используется, когда нужно заменить любой текст, начиная с определенной позиции.

Синтаксис

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

Текст    — это либо текст, либо ссылка на ячейку, содержащую текст, в котором подставляются знаки.

Стар_текст    — заменяемый текст.

Нов_текст    — текст, на который заменяется стар_текст.

Номер_вхождения    — определяет, какое вхождение текста стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то заменяется только это вхождение текста стар_текст. В противном случае, каждое вхождение текста стар_текст в текстовой строке заменяется на текст нов_текст.

Функция ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.

Синтаксис

ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)

Старый_текст    — текст, в котором желательно заменить некоторые знаки.

Нач_поз    — позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст.

Число_знаков    — число знаков в тексте старый_текст, которые заменяются текстом новый_текст.

Новый_текст    — текст, который заменяет знаки в тексте старый_текст.

Пример:

Формула =ПОДСТАВИТЬ(A2;"(095)";"(495)") заменит старый телефонный код Москвы на новый.


Функция СЖПРОБЕЛЫ удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать избыточные пробелы.

Синтаксис

СЖПРОБЕЛЫ(текст)