Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части. Для этого мы использовали инструмент в Excel «Текст по столбцам».
Несомненно, это очень важный и полезный и инструмент в Excel, который значительно может упростить множество задач. Но у данного способа есть небольшой недостаток. Если вам, например, постоянно присылают данные в определенном виде, а вам постоянно необходимо их делить, то это занимает определенное время, кроме того, если данные вам прислали заново, то вам снова нужно будет проделать все операции.
Пример 1. Делим текст с ФИО по столбцам с помощью формул
Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях. В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера.
У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.
Попробуем очень подробно описать план действия и разобьем решение задачи на несколько этапов.
Первым делом добавим вспомогательные столбцы, для промежуточных вычислений, чтобы вам было понятнее, а в конце все формулы объединим в одну.
Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу
и протянем вниз. Формулу объяснять не буду — смотрите предыдущую статью
Теперь нам необходимо найти порядковый номер второго пробела. Формула будет такая же, но с небольшим отличием. Если прописать такую же формулу, то функция найдет нам первый пробел, а нам нужен второй пробел. Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст. Мы видим, что второй пробел находится в любом случае после первого пробела, а позицию первого пробела мы уже нашли, значит прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ искать пробел начиная с первой буквы после первого пробела. Функция будет выглядеть следующим образом:
Далее протягиваем формулу и получаем позиции 1-го и 2-го пробела.
Приступаем к делению первой части текста — Фамилии
Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:
=ПСТР(текст; начальная_позиция; число_знаков), где
- текст — это ФИО, в нашем примере это ячейка A2;
- начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
- число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.
Формула будет выглядеть следующим образом:
Приступаем к делению второй части текста — Имя
Снова используем функцию =ПСТР(текст; начальная_позиция; число_знаков), где
- текст — это тот же текст ФИО, в нашем примере это ячейка A2;
- начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
- число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2
Получаем итоговую формулу:
Приступаем к делению третьей части текста — Отчество
И снова функция =ПСТР(текст; начальная_позиция; число_знаков), где
- текст — это тот же текст ФИО, в нашем примере это ячейка A2;
- начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
- число_знаков — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50
Получаем функцию
=ПСТР(A2;I2+1;50)
Далее выделяем все три ячейки и протягиваем формулы вниз и получаем нужный нам результат. На этом можно закончить, а можно промежуточные расчеты позиции пробелов прописать в сами формулы деления текста. Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(” «;A2;1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «;A2;H2+1) . Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)
Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается 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 с помощью формулы
Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.
Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.
Напомним, что вручную (без формул) это задача очень просто решается с помощью инструмента текст по столбцам, который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул.