Как сделать инициалы в excel. Объединение имени и фамилии. В этом видео подробно показано, как разделить текст по столбцам

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

Конечно можно написать формулу , которая извлечет нужные данные из ФИО, но даже опытному человеку потребуется 5-7 минут на это. А что если заранее не знаешь расположение фамилии в тексте? Гораздо проще и удобнее пользоваться готовыми функциями из надстройки VBA-Excel .

Найти фамилию в ФИО

После установки надстройки будет доступна новая функция =ФАМИЛИЯ(ФИО) , где

• ФИО - текст или ссылка на ячейку, которая содержит ФИО полностью

Задача данной функции - извлечь из ячейки только фамилию. Причем ей не важен порядок. Фамилия может идти как в начале, так в конце или середине текста.

Найти имя в ФИО

Аналогичная функция для имени =ИМЯ(ФИО) . Она извлекает имя из текста с ФИО, также не важно где расположенное.

Найти отчество в ФИО

Последняя функция из этой серии - функция =ОТЧЕСТВО(ФИО) для извлечения отчества.

Примеры работы с ФИО в Excel Вывести фамилию и инициалы

Одной из распространенных задач при составлении документов - это преобразование текста вида Фамилия Имя Отчество к тексту Фамилия И. О. Решить ее можно используя следующую формулу.

Как в excel 2007 разделить текст по столбцам на две, три и более ячеек. Обычно для примера используют разбивку ячеек с ФИО, чтоб данные были раздельно.

Как разделить текст по столбцам в экселе

Мне по работе пришлось разделять время в ячейках таблицы excel , пример: 0:00-15:00. А между значениями разделитель точка с запятой 0:00-15:00;16:00-17:00;22:00-24:00. Эти данные в таблице по дням, в ячейках разное количество значений между разделителем. Необходимо было разделить время по каждому дню, в столбик под датой проставить время, разделить время на начало и конец, посчитать разницу и все сложить вместе...

Этап первый. Разделить время в ячейках

Выделяем диапазон ячеек, на ленте нажимаем Данные-Текст по столбцам, появится Мастер текстов.

Ничего не меняем, если у вас переключатель стоит в положении: с разделителем, жмем далее.

Снимаем галку "знак табуляция", ставим галку "другой"

и в окошко вносим точку с запятой [ ; ]. Жмем либо далее, хотя там особо смотреть не чего, верней нажать готово. Будет задан вопрос: заменить содержимое ячеек? Ответ да!

Мы смогли разбить ячейку на столько значений, сколько их было между [ ; ]. Добавим пустые строки под ячейкой с датой, в количестве, ячеек правей столбца С.

Две ячейки-две строки. Выделяем ячейки, копируем,

и под первой ячейкой жмем правой кнопкой "Специальная вставка"- "Транспонировать".

Данные перенесутся из строки в столбец, проделаем все то же самое для остальных ячеек.

Получилось разделить текст в ячейке, оформить по дням, в столбик.

Этап второй. Как в экселе разделить ячейку на время начала и конца

Получим разницу во времени, снова прибегнув к мастеру текста. Продолжим разделять текст в ячейках на время начала и конца. Здесь разделителем является знак "-" минус.

Вынесем столбец за нашу таблицу, скопировав и вставив ячейки рядом. Работаем с мастером: Данные-Текст по столбцам, шаг первый ничего не меняем, на втором шаге в окошке другой [ - ] ставим минус, нажимаем готово.

Этап третий

Ставим формулу: время конца минус время начала,

формат ячеек Все форматы [ч]:мм,

протянем формулу вниз до конца. Мы получили время в часах. В соседней ячейке напишем: =К4*60 Enter. Получим время в минутах.

Правда с секундами... Не нашел способа лучше, чем скопировать столбец, вставить его в блокнот, нажать CTRL+H (Найти и заменить), в найти написал:00

а заменить оставил пустым....

Нажал заменить все...Текст без:00 скопировал из блокнота и вставил в таблицу.

С количеством часов поступил проще, скопировал в блокнот и вставил в таблицу. Одна проблема: 24 часа стоят как 24:00:00.

Не проблема, раз мы знаем, как в экселе разделить ячейку , то тут можно и Copy&Paste применить, но с хитростью - в ячейке перед 24:00 поставить апостроф ‘ -одиночную кавычку "24:00

и вставить всюду. Складывать автосуммой можно только минуты, сумму часов получим, разделив сумму минут на 60, а количество дней разделим полученное число часов на 24....

В моем примере нечто из 31 дня проработало 28 дней,из 744 часов -673 часа и из 44640 минут -40411 минут... Не важно что это, главное что на этом примере вы узнали как как в экселе разделить текст по столбцам в ячейке, как можно не стандартно использовать блокнот и менять формат ячеек, добавляя к ним апостроф.

В этом видео подробно показано, как разделить текст по столбцам:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD , не забывайте подписываться на канал в YouTube , там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

По столбцам, чтобы имя, отчество и фамилия были в разных ячейках. Как разделить текст в Excel по столбцам?

Итак, существует два способа разделить текст по столбцам.

1-й способ разделить текст по столбцам. Мастер текста

Выделите столбец с текстом который хотите разделить. Затем выберите в верхней панели Данные - Текст по столбцам . Появится окно Мастера текста (разбор).

Если хотите разделить текст по пробелам или другим символам выбирайте «с разделителями».

В открывшемся меню выбираем пробел или нужный символ.

Выбираем формат текста или нажимаете «готово». Текст разделен!

Если все данные имеют одинаковую длину, можно разделить по фиксированной ширине (см. картинку 2)

2-й способ. Разделить текст в Excel формулами. Функция ПСТР

Если необходимо разделить текст по столбцам с использованием формулы воспользуйтесь функциями =ПСТР() и =НАЙТИ().

Для того чтобы найти Фамилию в ячейку В2 занесите формулу

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

Где функция Найти будет находить кол-во символов до пробела, а функция =ПСТР() возвращать это кол-во символов.

Чтобы получить Имя формула усложняется (необходимо дать функциям знать где начинается имя т.е. заканчивается 1-й пробел), поэтому введите в ячейку C3

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

Чтобы найти Отчество в ячейку D3 занесите формулу

=ПСТР(A2;I2+1;50) получаем =ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)+1;50)

Выглядит тяжеловато, но если разобраться вы увидите, что все несложно, особенно с примером:

Как сцепить ячейки Имя, Отчество, Фамилия

Для того чтобы сцепить данные в ячейках воспользуйтесь функцией = СЦЕПИТЬ () или структурой с использованием & (амперсант) - символа сцепки, например внесите в ячейку D3 формулу

=B2&" "&C2

Очень часто с этим вопросом, задают вопрос

Приложение Excel позволяет разбивать текст в ячейках столбца на несколько подстрок и располагать их в отдельных колонках. При этом для разбивки используется специальный разделитель, который выбирается пользователем для каждого конкретного случая.

Для примера рассмотрим данные CSV файла. Каждая подстрока в нем разделена символом «точка с запятой» и помещена в двойные кавычки. Вставьте их в любое место Вашей рабочей книги:

"ФИО";"Дата рождения";"Пол";"Город проживания";"Профессия"
"Иванов Иван Иванович";"27.03.1985";"Мужской";"Воронеж";"Электрик"
"Петров Петр Петрович";"12.03.1976";"Мужской";"Москва";"Геолог"
"Кухтина Елизавета Федоровна";"11.12.1994";"Женский";"Казань";"Врач"

Перед разбивкой данных требуется выделить нужный диапазон, а затем кликнуть по пиктограмме «Текст по столбцам» в области «Работа с данными» на ленте вкладки «Данные».

На экране будет отображено окно мастера распределения текста:

Весь процесс состоит из трех шагов.

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

Выберите пункт «С разделителями» и нажмите кнопку «Далее».

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

Обратите внимание на то, что если разделители идут подряд, то каждый из них образует новый столбец, т.е. 2 таких символа создают 1 пустой столбец, 3 – 2 и т.д. Чтобы избавиться от этого часто нужного действия достаточно поставить галочку на поле «Считать последовательные разделители одним».

Может возникнуть ситуация, когда подстрока для создания столбца содержит символ-разделитель, который таковым не является. В подобных случаях строки должны быть заключены в одинарные либо двойные кавычки (в нашем примере исходных данных это присутствует), а в окне мастера нужно выбрать, какой ограничитель строк применить.

Имейте в виду то, что символы-разделители и ограничители удаляются из содержимого разделенных данных.

На любом описанным выше этапе можно нажать кнопку «Готово» для предоставления возможности приложению Excel самостоятельно завершить разделение текста в ячейках столбца. Но если Вы хотите контролировать весь процесс, то продолжайте нажимать «Далее».

Завершающим шагом является назначение форматирования новым колонкам и указания места для их расположения.

Из предоставлено только несколько форматов (типов данных), а поместить распределенный текст можно только на текущем листе.

Если материалы сайт Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

Здесь рассмотрим, как разделить текст в ячейке Excel на несколько ячеек. Например, предложение из первой ячейки разнести по словам в несколько других ячеек, ФИО, дату, т.д.
Например, у нас есть список с фамилиями, именами в одном столбце. Нам нужно написать фамилии в одном столбце, а имена в другом столбце. Например, чтобы определить пол людей в списке. Подробнее об этом читайте в статье "В Excel по имени определить пол" .
Выделяем ячейки, текст в которых нужно разделить или весь столбец.
Заходим на закладке «Данные» в раздел «Работа с данными» и выбираем функцию «Текст по столбцам». В появившемся окне «Мастер текстов», в строке «Формат исходных данных» указываем – «С разделителями». Нажимаем кнопку «Далее».

Здесь указываем в строке «Символом – разделителем является» - «пробел». Поставили «пробел», потому что в нашем списке имя и фамилия написаны через пробел. Если бы имя и фамилия (др. слова) были написаны через запятую, то мы бы указали разделителем «запятая».

В этом окне, в разделе «Образец разбора данных» сразу видно, какие столбцы у нас получатся. Нажимаем кнопку «Далее».
Здесь, в разделе «Поместить в:» указываем диапазон столбцов, в которые поместятся новые данные.

Внимание!

Столбцы этого диапазона должны быть пустыми, иначе информация в них сотрется и заменится на новую. Если нет пустых столбцов, можно предварительно вставить столбцы. Как это сделать, смотрите в статье «Как добавить строку, столбец в Excel» .
Можно указать формат Ячеек столбцов. Например, если вы делите столбец на дни и месяцы, год, то можно указать «Формат данных столбца» - «дата».
Нажимаем кнопку «Готово». Получилось так.
Так можно разделить текст из одной ячейки на большее количество столбцов. Например, здесь разделили дату отдельно по столбцам – число, месяц, год.

Для большей наглядности в таблице Excel можно выделить некоторые строки, столбцы (например, итоговые) выделить другим цветом, разделительной линией. Подробнее, смотрите в статье "Разделительная линия строк в таблице Excel" .
Можно разделить ячейку по диагонали и, в каждой части написать текст. Читайте, как это сделать, в статье "Как разделить ячейку в Excel" .
Удобнее работать в таблице, где в ячейках не видны нули. Как их убрать, скрыть, заменить, читайте в статье "

Функция получает в качестве параметра текстовую строку с виде "Фамилия имя отчество", и обрезает имя и отчество, оставляя лишь инициалы - в виде "Фамилия И. О."

Данную функцию можно использовать как UDF (определённые пользователем функции) в ячейках листа Excel .
(см. пример во вложении)

Описание особенностей функции - на форуме: programmersforum.ru/showpost.php?p=757147&postcount=6

Не существует точного формального алгоритма разделения ФИО на части.
Отделить фамилию от имени формально нельзя, не зная генеалогии и языка носителя. Можно только воспользоваться разнообразными эвристиками.

Кроме того, разные органы (в разное время) придерживались (-ются) разных взглядов на то как в том или ином случае должны выглядеть инициалы.
Одно дело документы УФМС - другое, регистраторы ЦБ, третье - оформление коммерческих документов.
Так, часть служб требует что бы 2-3 сложные короткие имена Юго-Восточной Азии не сокращались. А часть нет.

Да же с вопросом где должны стоять инициалы справа или слева - нет однозначности. Нет, конечно есть правила русского языка... Но превалируют правила делового оборота той или иной структуры (или просто как захочет левая задняя нога клерка принимающего документы)...

У многих возможно возникает вопрос: А откуда берётся весь этот зоопарк и зачем это всё нужно? Как правило, все эти Доны Педро - иностранные граждане получающие Российский паспорт на основании ранее выданных вне юрисдикции РФ документов. Ситуации правовые бывают разные. И достаточно часто в новый Российский паспорт пишется вариант транслитерации на кириллицу с языка носителя.
Российские паспорта с -оглы и -кызы выдаются сейчас в Татарстане. Мне встретился клиент которого по паспорту 2007 г. звали Мустафа Олег оглы...

Option Compare Text Public Function Инициалы(s As String , Optional ToLeft As Boolean = False ) Dim sv As Variant , sФ As String , sИ As String , sО As String , i As Long , k As Long Application.Volatile True If InStr(s, "." ) > 0 Or Len(Trim$(s)) = 0 Then Инициалы = s "Инициалы заданы явно или пустая строка Exit Function End If "Нормализация входной строки s = Replace(Application.Trim(s), Chr(30), "-" ) s = Replace(Replace(s, " -" , "-" ), "- " , "-" ) s = Replace(Replace(s, "" " , """ ), " "" , """ ) " О "Генри Александр; О" Генри Александр; Н" Гомо; Д" Тревиль sv = Split(s) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) If i < 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" "бей, бек, заде, зуль, ибн, кызы, оглы, оль, паша, уль, хан, шах, эд, эль i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Case "паша" , "хан" , "шах" , "шейх" i = i - 1 Case Else Select Case Right$(sv(i), 3) Case "вич" , "вна" If i >= 2 Then "Стандартное окончание русских отчеств sО = СropWord(sv(i)) Else "Имя типа Босан Славич sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) If k > 0 Then Select Case Mid$(sv(i), k + 1) Case "оглы" , "кызы" , "заде" , "угли" , "уулы" , "оол" "Вариант насаба «-оглы» и «-заде» типа Махмуд-оглы sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 If i = 0 Then sИ = sО sО = vbNullString End If End Select ElseIf i > 2 Then Select Case sv(i - 1) Case "ибн" , "бен" , "бин" sО = UCase(Left$(sv(i), 1)) & "." " Усерталь Алишер бен Сулейман i = i - 2 End Select Else " Бен Эдуард sИ = UCase(Left$(sv(i), 1)) If Len(sv(i)) > 1 Then sИ = sИ & "." i = i - 1 End If End Select End Select Select Case sv(0) Case "де" , "дел" , "дос" , "cент" , "ван" , "фон" , "цу" If i >= 2 Then sФ = sv(0) & " " & StrConv(sv(1), vbProperCase) sИ = СropWord(sv(2)) Else "Де Николай If Len(sИ) > 0 Then sФ = sv(0) & " " & StrConv(sv(1), vbProperCase) Else sФ = StrConv(sv(0), vbProperCase): sИ = СropWord(sv(1)) End If End If Case Else If Len(sФ) = 0 Then "Ещё не определили фамилию sФ = StrConv(sv(0), vbProperCase) If Len(sИ) = 0 Then sИ = СropWord(sv(1)) End If End Select If ToLeft Then Инициалы = sИ & sО & " " & sФ Else Инициалы = sФ & " " & sИ & sО End Function Public Function СropWord(s As Variant ) As String If Len(s) = 1 Then СropWord = s Else ss$ = UCase(Left$(s, 1)) & "." : k = InStr(s, "-" ) If k > 0 Then ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." СropWord = ss$ End If End Function

Вложение Размер

У меня было сотня случаев, когда из списка сотрудников необходимо было достать фамилию или имя. При этом список насчитывал минимум пару сотен человек и делать это в ручную было очень грустно. Хорошо, что в Excel есть все необходимые инструменты, позволяющие оптимизировать данный процесс и решить поставленную задачу за несколько минут. Текстовые формулы Excel, позволяют решать подобные задачи. Сейчас я вам все подробно объясню на примере нескольких актеров любезно предоставленных первой страницей Google.

Текстовые формулы Excel, которые нам понадобятся

Для решения поставленной задачи нам понадобится собственно Excel и следующие стандартные текстовые функции:

ПОИСК — позволяет найти текст или значение в тексте

ДЛСТР — подсчитывает количество символов в ячейке с текстом

ПРАВСИМВ — выдает заданной количество символов с конца текста

Наш особенно сообразительный читатель возможно уже догадался как решить нашу задачу, но я всё же объясню для всех по шагам как это нужно было бы мне 🙂

Для решения нашей задачи нам понадобиться:

Посчитать длину текста в ФИО с помощью функции ДЛСТР

Найти номер знака с которого начинается фамилия с помощью функции ПОИСК

Посчитать длинну фамилии как разницу между результатами пунктов 1 и 2

Вывести фамилию с помощью функции ПРАВСИМВ и длинны фамилии из третьего пункта.

Звучит как план к действию. Приступим!

Фукнция ДЛСТР

Данная текстовая функция Excel — одна из простейших. Ей просто нужно указать ячейку и она выдает количество символов в ней. Вы и сами можете убедиться на примере:

Функция ПОИСК

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

Важно отметить, что пробел или искомый текст в данной функции записывается в ковычках. В частности это будет примерно так =ПОИСК(» «; ‘ячейка с текстом’).

Учитывая, что фамилия находится после второго пробела мы будем использовать два поиска, один из них вложенный для поиска первого пробела, как на примере ниже:

Функция ПРАВСИМВ

У нас все готово, чтобы извлечь фамилию из ФИО и решить нашу задачку. Для этого в аргументах функции указываем ячейку с ФИО актера и количество символов рассчитываем как разницу между первым и вторым действием. В итоге собираем решение вместе.

Раннее мы рассматривали возможность на примере деления ФИО на составные части. Для этого мы использовали инструмент в Excel «Текст по столбцам».

Несомненно, это очень важный и полезный и инструмент в Excel, который значительно может упростить множество задач. Но у данного способа есть небольшой недостаток. Если вам, например, постоянно присылают данные в определенном виде, а вам постоянно необходимо их делить, то это занимает определенное время, кроме того, если данные вам прислали заново, то вам снова нужно будет проделать все операции.

Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию и , которую мы рассматривали в прошлых статьях. В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера.

У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.

Попробуем очень подробно описать план действия и разобьем решение задачи на несколько этапов.

Первым делом добавим вспомогательные столбцы, для промежуточных вычислений, чтобы вам было понятнее, а в конце все формулы объединим в одну.

Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу

НАЙТИ(" ";A2;1)

Теперь нам необходимо найти порядковый номер второго пробела. Формула будет такая же, но с небольшим отличием. Если прописать такую же формулу, то функция найдет нам первый пробел, а нам нужен второй пробел. Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст. Мы видим, что второй пробел находится в любом случае после первого пробела, а позицию первого пробела мы уже нашли, значит прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ искать пробел начиная с первой буквы после первого пробела. Функция будет выглядеть следующим образом:

Приступаем к делению первой части текста — Фамилии

Для этого мы воспользуемся функцией , напомню синтаксис данной функции:

ПСТР(текст ; начальная_позиция ; число_знаков ), где

текст — это ФИО, в нашем примере это ячейка A2;

начальная_позиция - в нашем случае это 1, то есть начиная с первой буквы;

число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.

Формула будет выглядеть следующим образом:

ПСТР(A2 ;1 ;H2-1 )

Приступаем к делению второй части текста — Имя

Снова используем функцию =ПСТР(текст ; начальная_позиция ; число_знаков ), где

текст — это тот же текст ФИО, в нашем примере это ячейка A2;

начальная_позиция - в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1 ;

число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2

Получаем итоговую формулу:

ПСТР(A2 ;H2+1 ;I2-H2 )

Приступаем к делению третьей части текста — Отчество

И снова функция =ПСТР(текст ; начальная_позиция ; число_знаков ), где

текст — это тот же текст ФИО, в нашем примере это ячейка A2 ;

начальная_позиция - Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1 ;

число_знаков — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50

Получаем функцию

ПСТР(A2 ;I2+1 ;50 )

Далее выделяем все три ячейки и протягиваем формулы вниз и получаем нужный нам результат. На этом можно закончить, а можно промежуточные расчеты позиции пробелов прописать в сами формулы деления текста. Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 - НАЙТИ(" ";A2;1) , а расчет второго пробела в ячейке I2 — НАЙТИ(" ";A2;H2 +1) . Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу

Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией

• Фамилия =ПСТР(A2;1;H2 -1) получаем =ПСТР(A2;1;НАЙТИ(" ";A2;1) -1)
• Имя =ПСТР(A2;H2 +1;I2 -H2 ) получаем =ПСТР(A2;НАЙТИ(" ";A2;1) +1;
  НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1) -НАЙТИ(" ";A2;1) )
• Отчество =ПСТР(A2;I2 +1;50) получаем =ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1) +1;50)

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

Надеемся, что данный пример наглядно показал вам, как полезны текстовые функции Excel для работы с текстом и как они позволяют делить текст автоматически с помощью формул однотипные данные. Если вам понравилась статья, то будем благодарны за нажатие на +1 и мне нравится. Подписывайтесь и вступайте в нашу группу вконтакте.

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.

Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.

Напомним, что вручную (без формул) это задача очень просто решается с помощью , который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул.

Для начала необходимо найти общий разделить, по которому мы будет разбивать текст. В нашем случае это запятая, но например в первой задаче мы делили ФИО и разделитель был пробел. Наш второй пример более универсальный (более удобный при большом количестве данных), так например мы удобно могли бы делить не только ФИО по отдельным ячейкам, а целое предложение — каждое слово в отдельную ячейку. Собственно такой вопрос поступил в комментариях, поэтому было решено дополнить эту статью.

Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.

Теперь основная суть приема.

Шаг 1 . В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки

НАЙТИ(B1;A1;1 )

То есть ищем запятую, в тексте, начиная с позиции 1

НАЙТИ($B1 ;$A1;D1+1 )

Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.

Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.

Пропишем формулу и протянем ее вниз.

Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее

Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.

ПСТР(A1;1 ;D1-1 )

Начальная позиция у нас 1 , количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз

Шаг 5 . Находимо второе слово так же с помощью функции ПСТР в ячейке P1

ПСТР($A1;D1+1;E1-D1-1)

Начальная позиция второго числа у нас начинается после первой запятой. Позиция первой запятой у нас есть в ячейке D1, прибавим единицу и получим начальную позицию нашего второго числа.

Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1

Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.

Шаг 6 . Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.

Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением. Так же можно сгруппировать и свернуть вспомогательные столбцы, чтобы они не мешали. Получим итоговое решение задачи

ЕСЛИОШИБКА(ПСТР($A1;D1+1;E1-D1-1); "")

Примечание . Первую позицию разделителя и первое деление слова мы делали отлично от других и из-за этого могли протянуть формулу только со вторых значений. Во время написания задачи я заметил, что можно было бы упростить задачу. Для этого в столбце С нужно было прописать 0 значения первого разделителя. После этого находим значение первого разделителя

НАЙТИ($B1;$A1;C1+1)

а первого текста как

ПСТР($A1;C1+1;D1-C1-1)

После этого можно сразу протягивать формулу на остальные значения. Именно этот вариант оставляю как пример для скачивания. В принципе файлом можно пользоваться как шаблоном. В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.

Внимание! В комментариях заметили, что так как в конце текста у нас нет разделителя, то у нас не считается количество символов от последнего разделителя до конца строки, поэтому последний разделенный текст отсутствует. Чтобы решить вопрос можно либо на первом шаге добавить вспомогательный столбец радом с исходным текстом, где сцепить этот текст с разделителем. Таким образом у нас получится что на конце текста будет разделитель, значит наши формулы посчитают его позицию и все будет работать.

Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.

ЕСЛИОШИБКА(НАЙТИ($B1;$A1;C1+1);1000)

Excel для Office 365 Excel для Office 365 для Mac Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel Online Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel Mobile Excel Starter 2010 Меньше

Предположим, что вы хотите создать одного полное имя столбца путем объединения двух других столбцов, имени и фамилии. Для объединения имени и фамилии используется функция СЦЕПИТЬ или оператор амперсанд (&).

Важно: Эта функция в Excel 2016Excel Mobile и Excel Online, был заменен с помощью функции ОБЪЕДИНЕНИЯ . Несмотря на то, что функция СЦЕПИТЬ по-прежнему доступен для обеспечения обратной совместимости, следует использовать с этого момента ОБЪЕДИНЕНИЯ. Это связано с СЦЕПИТЬ могут быть недоступны в будущих версиях Excel.

Пример

Скопируйте приведенный ниже на пустой лист.

Фамилия Владимир Формула Описание (результат) Объединяет указанные выше имена, разделенных пробелом (Белова) Объединяет указанные выше имена, разделяя их точкой с запятой (Новиков, Павел) СЦЕПИТЬ(A2;" ";B2)