Вместо спойлера: публикую материал с небольшими правками со своего заброшенного блога. В случае положительной реакции читателей - продолжу публикации на данную тему.
Поговорим о том, как работать с текстом в Excel.
Поставим себе следующую задачу: Разделить на отдельные слова текст из ячеек в столбце А:
Для реализации поставленной задачи, нам понадобится целых 6 функций для работы с текстом в Excel:
НАЙТИ
ЛЕВСИМВ
ПСТР
ПРАВСИМВ
ДЛСТР
ПОИСК
В первую очередь отделим фамилию. Для этого нам понадобится функция для печати n-го количество знаков с начала строки(с лева строки):
=ЛЕВСИМВ(текст; [количество знаков])
Кроме того, нам надо найти положение первого пробела в строке в столбце "А". Для этого воспользуемся функцией
=НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция]), где:
искомый_текст - текст, который мы ищем, в нашем случае это " " (знак пробела, вводить нужно с кавычками);
просматриваемый_текст - исходный текст, в котором мы ищем знак " ", т.е. ячейка "А1"
нач_позиция - позиция в "просматриваемый текст" с которого начинать поиск. В нашем случае этот параметр можно опустить, либо указать "1" - т.е. просмотр с первого символа.
Объединяем две функции в следующую конструкцию (в ячейку "В1"):
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)
Обратите внимание, что после функции НАЙТИ() у нас строит "-1". Дело в том, что функция НАЙТИ возвращает позицию с учетом найденного символа, т.е. для случая в ячейке "А1", вернет не 6 а 7, а нам нужно отрезать именно 6 знаков.
Все, первый шаг из трех мы сделали - фамилия у нас уже отделилась. Если все сделать правильно, то скопировав формулу на остальные ячейки в столбце "В", получим такой результат:
Теперь попробуем отделить имена наших жертв в столбец "С":)). Задача усложняется, теперь, нам нужно найти как начальную позицию для вырезания, так и конечную. Основной функцией у нас будет:
=ПСТР(текст;начальная_позиция;количество_знаков), где:
текст - текст, из которого вырезаем нужную строку, у нас "А1"
начальная_позиция - начальная позиция в тексте, из которого будет вырезан фрагмент
количество_знаков - количество знаков, которое необходимо вырезать
Чтобы найти начальную позицию, воспользуемся функциями ДЛСТР(текст), которая возвращает количество знаков в строке - "текст". Для этого, нам достаточно посчитать количество знаков в фамилии, которая у нас уже есть в строке "В1" и прибавить 2 знака, чтобы функция ПСТР не вырезала левый пробел ("Иванов" - 6 знаков, +1 пробел, +1 символ от имени, с которого необходимо начинать вырезания - итого, начинаем с 8-го символа).
Чтобы определится с количеством вырезаемых знаков, воспользуемся функцией
=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция]), где:
искомый_текст - текст, который мы ищем, в нашем случае это пробел " "
текст_для_поиска - текст, в котором необходимо произвести поиск, у нас "А1"
нач_позиция - позиция в "текст_для_поиска" с которого необходимо начать поиск
В итоге, у нас должна получится следующая конструкция из функций: =ПСТР(A1;ДЛСТР(B1)+2;ПОИСК(" ";A1;ДЛСТР(B1)+2)-(ДЛСТР(B1)+2))
Алгоритм работы:
При помощи функции =ПСТР из текста в ячейке "А1" вырезаем текст, начиная с позиции равной длине текста в ячейке "В1" + 2 знака, эквивалентные пробелу и следующему за ним символу; количество символов для вырезания определяется как разность позиции, которую определяем функцией =ПОИСК второго знака пробел " " в тексте "А1" с начальной позицией для поиска, переходящей за первый пробел в тексте "А1" (часть =ДЛСТР(В1)+2) и длиной текста в ячейке В1 + 2 знака на пробел (фактически, определяем порядковый номер второго пробела и вычитаем длину уже известной части - фамилии, с учетом всех пробелов).
В итоге, при копировании формулы в остальные ячейки, должно получится следующее:
Третий шаг - вырезаем отчество. Упростим себе задачу, воспользовавшись функцией =ПРАВСИМВ(текст;[число_знаков])
Данная функция вырезает заданное число знаков, начиная с конца строки.
При помощи =ДЛСТР определяем общую длину текста в ячейке "А1" и отнимаем длину текста в "В1" и "С1", учитывая 2 пробела. Получаем такую конструкцию: =ПРАВСИМВ(A1;ДЛСТР(A1)-2-ДЛСТР(B1)-ДЛСТР(C1))
Скопируем получившуюся формулу в нижние строки и получаем результат:
Таким способом можно разделять текст в ячейках, признаком деления может быть любой символ.
Чтобы выполнить обратную задачу - сцепить текст из нескольких ячеек в одну, можно воспользоваться специальной текстовой функцией Excel: =СЦЕПИТЬ(текст1;текст2;...) Так, чтобы в ячейке E1 получить сочетание И.О.Ф. достаточно добавить туда следующую функцию:
=СЦЕПИТЬ(C1;" ";D1;" ";B1)