Как разделить текст в Excel с помощью формулы
Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части. Для этого мы использовали инструмент в Excel «Текст по столбцам».
Несомненно, это очень важный и полезный и инструмент в Excel, который значительно может упростить множество задач. Но у данного способа есть небольшой недостаток. Если вам, например, постоянно присылают данные в определенном виде, а вам постоянно необходимо их делить, то это занимает определенное время, кроме того, если данные вам прислали заново, то вам снова нужно будет проделать все операции.
Пример 1. Делим текст с ФИО по столбцам с помощью формул
Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях. В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера.
У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.
Попробуем очень подробно описать план действия и разобьем решение задачи на несколько этапов.
Первым делом добавим вспомогательные столбцы, для промежуточных вычислений, чтобы вам было понятнее, а в конце все формулы объединим в одну.
Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу
=НАЙТИ(" ";A2;1)и протянем вниз. Формулу объяснять не буду — смотрите предыдущую статью
Теперь нам необходимо найти порядковый номер второго пробела. Формула будет такая же, но с небольшим отличием. Если прописать такую же формулу, то функция найдет нам первый пробел, а нам нужен второй пробел. Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст. Мы видим, что второй пробел находится в любом случае после первого пробела, а позицию первого пробела мы уже нашли, значит прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ искать пробел начиная с первой буквы после первого пробела. Функция будет выглядеть следующим образом:
=НАЙТИ(" ";A2;H2+1)
Далее протягиваем формулу и получаем позиции 1-го и 2-го пробела.
Приступаем к делению первой части текста — Фамилии
Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:
=ПСТР(текст; начальная_позиция; число_знаков), где
- текст — это ФИО, в нашем примере это ячейка 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 вложенную формулу НАЙТИ(» «;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 с помощью формулы
Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.
Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.
Напомним, что вручную (без формул) это задача очень просто решается с помощью инструмента текст по столбцам, который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул.
Для начала необходимо найти общий разделить, по которому мы будет разбивать текст. В нашем случае это запятая, но например в первой задаче мы делили ФИО и разделитель был пробел. Наш второй пример более универсальный (более удобный при большом количестве данных), так например мы удобно могли бы делить не только ФИО по отдельным ячейкам, а целое предложение — каждое слово в отдельную ячейку. Собственно такой вопрос поступил в комментариях, поэтому было решено дополнить эту статью.
Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.
Теперь основная суть приема.
Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки
=НАЙТИ(B1;A1;1)
То есть ищем запятую, в тексте, начиная с позиции 1
Шаг 2. Далее в ячейке E1 прописываем формулу для нахождения второго знака (в нашем случае запятой). Формула аналогичная, но с небольшими изменениями.
=НАЙТИ($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)
Таким образом последний текст будет рассчитываться начиная от последней запятой до чуть меньше 1000 знаков, то есть до конца строки, что нам и требуется.
Оба варианта выложу для скачивания.
Скачать пример: Как разделить текст по столбцам с помощью функции_1.xlsx (исправлено: доп поле)
Скачать пример: Как разделить текст по столбцам с помощью функции_2.xlsx (исправлено: заведомо большое число)
последнее число то не определилось! решение не законченное
Вложение 1.xlsx
подскажите как разделить значения:
10-16.5 Cultor Skid Steer 20 нс8 TL Сербия клюшка Погрузчик
10-16.5 — посадочный размер
Cultor — производитель
Skid Steer 20 — Тип протектора
нс8 — Слойность
TL — камерная бескамерная.
Не знаю как это все разделить
Сначала делите по разделителю «пробел» затем собираете некоторые данные, например тип, где может встречаться пробел.
с помощью блокнота разделить будет гораздо быстрее и проще))
Это как с помощью блокнота?
Вложение 1.xlsx
А можно ли разделить вот такую ячейку построчно:
1
2
3
4
5
6
Можно, просто в качестве разделителя (вместо точки запятой в файле) нужно указать символ переноса строки
укажите там =Символ(10)
Подробнее про это тут: https://sirexcel.ru/priemi-excel/rabota-s-tekstom/kak-v-excel-sdelat-perenos-teksta-v-yachejke/
Направил комментарий, но он не опубликован…
Есть предмодерация или следует просто повторить коммент?
Вложение Primer.xlsx
Здравствуйте! Весьма полезная статья, спасибо!
Подскажите, пожалуйста, можно ли разделить текст, когда в некоторых разделяемых ячейках может быть несколько строк текста, и, при этом, часть текста (ненужную после разделения) удалить. См. пример.
Заранее благодарен!
Да, конечно. Вам нужно просто создавать формулу для каждого отдельного поля. Все данные у вас есть за что можно зацепиться
Спасибо.
Возможно, мой вопрос был не совсем чётко сформулирован.
В приложенном для примера файле указаны лишь две ячейки из столбца, состоящего из множества строк.
При этом, отличие ячеек друг от друга заключается в том, что в некоторых из них записи только на одной строке, в других — подобных записей в одной ячейке несколько, и размещены они каждая в отдельной строке ячейки.
Как Вы полагаете, может ли быть составлена формула, которая будет корректно разделять текст в ячейках, независимо от того на скольких строках в ячейке имеются записи (т.е. на одной строке либо на нескольких)?
Ведь в случае, если в ячейке содержатся подобные записи на нескольких строках (а не на одной), получается, что те элементы первоначального текста записи, подлежащей разделению, которые могут быть определены как «разделители» для разделения текста, будут встречаться в ячейке не единожды, а уже по нескольку раз (в пределах одной ячейки, я зависимости от количества строк в ней).
Вложение -Microsoft-Office-Excel.xlsx
Привет! Можно ли так сделать например: /ручка/ 10 шт -> (шт не написано просто чтобы понятнее было вам)/ 10руб (руб тоже не написано)/ и этого нужно делать так по отдельности /ручка/1шт/10руб/
Если я правильно понял, то только макросом. Есть конечно вариант формулами, но зависит от того как располагается и как нужно чтобы располагались другие данные, но явно будет все нагромождено формулами.
Вложение .xls
Здравствуйте ! Помогите решить аналогичную задачу: есть база автомобилей, но их часть находится в одной ячейке. Каким образом разделить, чтобы каждый автомобиль был в отдельной строке, и можно ли сделать этот процесс автоматическим (уж очень много строк). Спасибо!
с формулами можно но не очень удобно. Если данная процедура нужно не постоянно, то это можно сделать вручную сразу весь материал
Сначала нужно поменять переносы строк на какой-нибудь символ который не встречается у вас, например на |
Как это сделать описано тут: https://sirexcel.ru/priemi-excel/rabota-s-tekstom/kak-v-excel-sdelat-perenos-teksta-v-yachejke/
Лучше всего воспользоваться способом ПОИСК — ЗАМЕНА
Далее разбить полученный текст по столбцам https://sirexcel.ru/osvaivaem-excel/kak-razbit-tekst-po-stolbcam-v-excel/
В качестве разделителя используйте тот же символ, у нас это |
Теперь каждый автомобиль будет в отдельной ячейке.
Если нужно отобразить их списком, то воспользуйтесь транспонирование данных
https://sirexcel.ru/priemi-excel/trasp-avtomaticheskoe-transponirovane-dannyx-s-pomoshhyu-v-excel/
Добрый день! Подскажите как разбить текст по столбцам! Прочитал статью. но не понял( Начальный текст всегда с одинаковым количеством знаков, одной длины. Пример: 1825R0001WD000014W
Нужно разбить первый столбец- первые четыре цифры, потом одна буква, потом 4 цифры, потом 4 столбца по 2 буквы или по 2 нолика, потом две цифры и одна буква)
Жду ответа! спасибо!
Если у вас одинаковые тексты, то используйте ПСТР просто указывайте с какой позиции начинать и на сколько знаков.
Добрый день, подскажите пожалуйста формулы чтобы из пяти предоставленных слов в первой ячейке, например «Lorem ipsum dolor sit amet», в пяти последующий ячейках выводилось отдельно с первого по пятое слово. То есть нужно пять формул получается, чтобы сначала было Lorem, затем ipsum, и так далее. Спасибо большое!
В этой статье про это и рассказывается. Просто находится подряд пробелы по порядку. Первый как в статье, когда ищите второй, то порядковый номер первого пробела + 1 можно использовать как начальную позицию где искать (это третий аргумент функции НАЙТИ) и так далее.
Ну в том то и дело, что у меня не получается создать все пять формул без ошибок, первые две да, и то если слова всего два то ошибка, на третьем слове публикует всё с третьего и по пятое, то есть просто первые два не публикует, а всё что за ними выводит, по сути не третье слово, а остаток строки без первых двух слов. Помоги пожалуйста с более точными формулами рассчитанными на пять слов. Спасибо ещё раз.
Вы правы. Спасибо что заметили и написали. Поменял пример файла. добавил вспомогательный столбец (сцепил текст и разделитель) Теперь должно корректно работать.
Огромное спасибо! Всё работает! Очень круто! Вы лучший!
Дописал эту статью с вашим вопросом. Можете сделать по аналогии
Вложение
Спасибо огромное, всё получилось. И формула для скрытия сообщений об ошибках прекрасна. Однако обнаружилась новая проблемка, вы можете заметить её даже в ваших расчётах. Не отображается последнее слово. То есть если слов на одно больше чем рассчитано, то предпоследнее отображается по формуле, а если их ровно 5 предположим, то отображается 4, а 5 ячейчка пустая. Прикрепляю скриншот с пометками.
Здравствуйте как отедлить крайнюю букву от слова
например АленаА
нужно чтобы было так Алена А
И так по списку
Только макросом либо скачайте бесплатную программу notepad++ (это продвинутый блокнот», ищите в поисковике)
Копируйте туда весь текст, открываете окно замены текста, переставляете галочку на «Режим поиска», «Регулярные выражения».
В поле «Найти» прописываете: ([А-Я])
В поле заменить на: \1
Перед косой чертой должен быть пробел
Должна стоять галочка «Учитывать регистр»
И делаете замену по всему тексту. В результате перед каждой заглавной буквой появится пробел Потом копируете текст обратно в Excel и используете.
Минус в том, что это нужно делать вручную. Но если разово, то способ отлично подойдет
Как разделить дату из одной ячейки (вида 12.06.1954) по трём ячейкам: день, месяц, год?
Очень просто
=ГОД()
=МЕСЯЦ()
=ДЕНЬ()
Подскажите пожалуйста, как вытащить в отдельную ячейку текст, который идет после определенного стандартного текста.
Эта статья как раз про это. Что именно не получается?
Добрый день, такой вопрос мне нужно разделить телефон и e-mail в разные столбцы но не во всех ячейках есть и то и иное, а в некоторых и то и иное по два разных телефона или по две почты. есть ли решение. очень буду благодарен.
Этой информации не достаточно, чтобы ответить на вопрос. Нужно искать закономерности. Если есть общий разделитель, то используйте текст по столбцам https://sirexcel.ru/osvaivaem-excel/kak-razbit-tekst-po-stolbcam-v-excel/
как настроить автоматическое копирование текста из нескольких ячеек в одну на другом листе
Просто копирование, тогда только макросом.
Скажите как разбить на два столбца буквы и цифры отдельно:
0122NMP
0899MKU
63223
0337MK
0146MKU
0893NMP
136NMS
0068MS
0230MK
0580NMU
092nmu
26Me
Написал две статьи:
Как вытащить из текста только буквы
Как вытащить из текста только цифры
Их и используйте. Если у вас Excel 2007 и выше, то не забудьте сохранить файл в формате xlsm (с поддержкой макросов).
Либо можете разово использовать, а потом сохранить как значения уже без функции.
Привет!
помогите, пожалуйста, с такой задачей. Необходимо разделить столбец с данными. Данные разделены запятыми, но проблема в том, что необходимо отделить последние 2 раздела в строке в один столбец, а те данные, что находятся перед этими разделами, в другой.
Не совсем понятна задача. Хорошо бы посмотреть пример
Подскажите как отобразить число 17602059650 в виде 176-020-596-50 с помощью формулы ПСТР или аналог., нужно изм. длинный список
Если количество цифр одинаковое, то можно вот так
=ПСТР(A1;1;3)&»-«&ПСТР(A1;4;3)&»-«&ПСТР(A1;7;3)&»-«&ПСТР(A1;10;2)
Подскажите пожалуйста, если у меня вместо пробелов точки, тест я поделила, но в разделенном виде видны точки, как их можно убрать? ( не используя функцию заменить)
Когда используете функцию ПСТР
ПСТР(текст; начальная_позиция; число_знаков)
в аргументе число_знаков укажите на (число_знаков — 1) чтобы слово было без последнего символа. Если я вас неправильно понял, то напишите подробнее
Спасибо вам большое! все получилось .
А просто Данные — Такст по столбцам не подойдет для решения данной задачи?
Подойдет, в статье про это написано и дана ссылка. Тут же рассматривается пример, когда нам нужно делать это постоянно и желательно автоматически. В случае с «Текст по столбцам» придется делать это постоянно в ручную
Спасибо за статью, но есть нюанс, а как сделать что бы осуществлялась проверка на отчество, не у всех оно есть
способов много. Например на шаге определения второго пробела. если он не находится, то отчества нет. Делаем проверку, если поиск пробела выдает ошибку, то возвращаем пусто, если нет, то вычисляем и действуем так же как в статье
Подскажите пожалуйста, нужно разделить ячейку после 25 символа на 2 других ячейки(первая и вторая часть)
но при том условии что б слово не обрезалось на пополам, а переходило во вторую ячейку. Как это осуществить?
Можно например использовать дважды функцию ПСТР: https://sirexcel.ru/priemi-excel/funkcii/funkciya-pstr-v-excel-s-primerom-i-formuloj/
Получится что-то в этом роде
=ПСТР(A1;1;25)
=ПСТР(A1;26;50)
оно переносит,но слова обрезаются
тогда ваша задача не понятна. Приведите пример как было и как должно стать
«База строительных организаций» 29 символов. Функция ПСТР разделяет на 2 ячейки, но обрезает и выходит «База строительных организ» «аций», а нужно что б в этом случае все слово»организаций» переходило во вторую ячейку.
Кстати, нет. Тут я не учел именно ваш вариант, когда дальше нет слов.
Теперь понятно. В этом случае делаем следующее
Ищем пробел начиная с 25-го символа
=НАЙТИ(» «;A1;25)
Допустим текст «База строительных организаций и другое»
В данном случае функция вернет нам число 30 (то есть пробел находится 30-ым по счету.
Если текст будет совсем короткий — меньше 25 символов, то функция вернет ошибку.
После этого делаем проверку с помощью функции ЕСЛИ
Если ошибка, то пусто
ЕСЛИ нет, то
ПСТР(A1;НАЙТИ(» «;A1;25);50)
если нужно убрать еще и пробел, то
ПСТР(A1;НАЙТИ(» «;A1;25)+1;50)
Скажите пожалуйста, можно ли сделать так: в ячейке дан целый ряд чисел без пробелов, необходимо найти одну из цифр и при этом в другой ячейке появляется ответ «Да» или «Нет», соответственно если такая цифра есть в ячейке, то пишется Да, если нет — Нет. Надеюсь понятно объяснила.
Да, можно. Это делается с помощью функции НАЙТИ и ЕСЛИ