Делим слипшийся текст на части

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

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

Поехали..

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные - Текст по столбцам (Data - Text to columns). Появится окно Мастера разбора текстов:

text_to_columns1.png

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

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) - необходимо указать какой именно символ является разделителем:

text_to_columns2.png

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании "Иванов, Манн и Фарбер") не делился по запятой
внутри названия.

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

  • общий - оставит данные как есть - подходит в большинстве случаев
  • дата - необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
  • текстовый - этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

text_to_columns3.png

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

  • в Excel 2003 и старше - меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
  • в Excel 2007 и новее - вкладка Разработчик - Редактор Visual Basic (Developer - Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert - Module) и копируем туда текст вот этой пользовательской функции:

Function Substring(Txt, Delimiter, n) As String
Dim x As Variant
    x = Split(Txt, Delimiter)
    If n > 0 And n - 1 <= UBound(x) Then
        Substring = x(n - 1)
    Else
        Substring = ""
    End If
End Function

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

где

  • Txt - адрес ячейки с текстом, который делим
  • Delimeter - символ-разделитель (пробел, запятая и т.д.)
  • n - порядковый номер извлекаемого фрагмента

Например:

text_to_columns4.png

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО "ИвановИванИванович"), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

Function CutWords(Txt As Range) As String
    Dim Out$
    If Len(Txt) = 0 Then Exit Function
    Out = Mid(Txt, 1, 1)
    
    For i = 2 To Len(Txt)
        If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then
            Out = Out & Mid(Txt, i, 1) & " "
        Else
            Out = Out & Mid(Txt, i, 1)
        End If
    Next i
    CutWords = Out
End Function

Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:

разделение слипшегося текста без пробелов

 Ссылки по теме

 


Наталья
10.11.2012 11:02:38
Добрый день, а есть ли способ наоборот соединить текст в одну ячейку из нескольких?
10.11.2012 11:09:22
Использовать функцию СЦЕПИТЬ (CONCATENATE) из категории Текстовые.
Или символ амперсанда: =A1&" "&B1
Если в А1 будет "Наталья", а в В1 "Петровна", то на выходе такая формула выдаст "Наталья Петровна"
Сергей
10.11.2012 11:04:35
Большое спасибо за полезные и нужные советы!!!
Ольга
10.11.2012 11:05:14
Спасибо!!!!! Мне в работе приходится делить размеры, например такие 17.25*35 и их может быть более 100 на одном листе... сколько времени уходит, а ошибки недопустимы.
Madonna
10.11.2012 11:06:58
Спасибо! Классная вещь! Раньше я это делала с помощью Word. Но это было очень долго(((.
Юлия
10.11.2012 11:10:11
Господа, а если в ячейке Фамилия от второй строки отделена alt+enter, а на второй строке имя и отчество разделены пробелом, тогда как разбить на три столбца?
10.11.2012 11:11:11
Тогда во втором способе используйте в качестве символа-разделителя функцию СИМВОЛ(10)
16.07.2013 14:03:34
если нет желания мучиться со скриптами, то:

=ПОДСТАВИТЬ(C46;СИМВОЛ(10);"#")
 
функция подставит вместо всех alt+enter в ячейке символ #.

а уже после этого можно мастером сделать то, для чего все мы тут собрались...
12.05.2013 11:39:00
Замечательно.
13.06.2013 15:17:52
День добрый!
Коллеги, а подскажите - как работает в этом окне Ограничитель строк?
Наверняка какая-то полезная весчь, только ума не приложу - как использовать... :)
02.12.2013 20:51:01
То, что заключено в символы-ограничители не разделяется, даже если внутри есть символ-разделитель. Например, название книги в кавычках не должно делиться по запятой в названии: "Моя жизнь, мои достижения" (Г.Форд)
14.08.2013 14:27:16
добрый день. не подскажете как разделить текст на строки ? есть  бланк  - поля определенной ширины. при заполнении текстом поля - продолжение должно быть перенесено на следующую строку . как это можно автоматизировать . исходные данные- текст - планирую хранить в БД
07.11.2013 17:47:14
Здравствуйте. Скажите пожалуйста, а как разделить текст так, чтобы в ячейке появились все элементы текста, кроме последнего?
02.12.2013 20:49:31
На последнем шаге Мастера разбора текстов выделить последний столбец и выбрать Пропустить столбец.
21.11.2013 11:16:06
Добрый день!

Скажите можно ли разделить корректно при помощи мастера текстов вот этот текст:

01Монитор Acer V223WEbd s/n ETLC30811701502E314275
разделить так:

1 ячейка  01Монитор Acer V223WEbd
2.ячейка  ETLC30811701502E314275



02 НЖМД  2.0TbSeagate ST32000644NSs/n 9WM0GAK4
разделить так:

1 ячейка  НЖМД  2.0TbSeagate ST32000644NS
2 ячейка 9WM0GAK4

У меня получается  всё по разным ячейкам :)


02.12.2013 20:48:34
Дмитрий, я бы на вашем месте заменил (Ctrl+H) символы 's/n" на какой-нибудь экзотический символ, которого нет и не может встречаться в этих данных (например %). А потом разбил с помощью мастера текста по этому символу, как разделителю.
28.03.2014 11:27:59
Добрый день.
не могу найти формулу для превращение Фамилии Имя Отчества в Фамилию И.О.
Фамилия Имя Отчество в ОДНОЙ ячейке.
Создала такое:
=СЦЕПИТЬ(ЛЕВСИМВ(СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);1));ПСТР(СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);1)+1;1);".";ПСТР(СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);1)+1)+1;1);"." )
есть возможность как-то упростить формулу?
29.03.2014 17:42:55
Марина, вполне компактная формула для такой задачи. Можно еще макрофункцию под это дело писать - будет короче, но не факт, что быстрее работать.
05.06.2014 13:11:25
Добрый день, Николай.

Воспользовался Вашим макросом из http://www.planetaexcel.ru/techniques/12/45/
Получив результат, решил пойти дальше, разбив пути к файлам по столбцам. Разбить-то разбил без проблем. Но т.к. в папке есть вложенные папки, а в них могут быть и ещё, т.е. пути к  конечным файлам имеют разную длину.  Хотелось так: 1-й столбец - основная папка, 2-й столбец - папка первого вложения, 3-й - папка следующего вложения,... последний столбец - собственно файл. А  в результате получилось не совсем так, 1-й столбец конечно заполнился, а вот с остальными пошла чехарда. В качестве разделителя выбрал слеш \.
Что можно предпринять?
04.07.2014 10:04:55
Добрый день. А есть функция для разделения например такого же текста, только на строки, а не столбцы?
25.07.2014 11:24:03
здравствуйте! подскажите, как разделить
300-9179- 00147
27.07.2014 14:28:22
А в чем проблема? Выделите ячейку, запустите Мастер разбора текстов как описано в статье и поделите по дефису.
01.09.2014 11:39:13
Добрый день,
Почитал посмотрел, сделал, получилось!
Вопрос возник:
Например: 123 абв один а
Делим: с помощью VBA по пробелам, выбираем что отобразить.
Вопрос: Мне надо отобразить все после первого пробела. Как сделать?

P.S. Извините если кто то задавал этот вопрос, я посмотрел, вроде нет.
05.06.2015 11:57:45
Количество символов до первого пробела всегда три?
Если да, то отрезать функцией "Правсимв" до 4 -й позиции.
Ох, не посмотрел на дату))
05.06.2015 12:12:07
всегда разное решил это способом
"=ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(" ";A1;1))"
=) спасибо все равно)
25.09.2014 10:17:18
Николай, добрый день. Пользовалась раньше таким разделением ФИО по столбцам, пока не появился в списке работников  Мо Рен Сам Александр Александрович, которого он естественно разбил на 5 столбцов. Как поступить в этом случае?
27.09.2014 09:31:16
А как тут можно поступить? Если он такой один, то поправить руками. Если у вас половина списка таких товарищей, то выдергивать последнее слово в отдельную ячейку громоздкой формулой типа:
=ПРАВСИМВ(текст;ДЛСТР(текст)-НАЙТИ("*";ПОДСТАВИТЬ(текст;" ";"*";ДЛСТР(текст)-ДЛСТР(ПОДСТАВИТЬ(текст;" ";"")))))
где текст - ссылка на ячейку с ФИО.
17.11.2014 22:50:43
Скажите,плз, как в ячейке разбить слипшиеся слова?
23.11.2014 09:51:00
Если вы имеете ввиду ситуацию, когда в ячейке слова СБольшойБуквыНоБезПробелов, то нужно писать макрос, который будет вставлять недостающие пробелы. Штатными средствами тут сложно победить.
27.12.2014 23:41:37
Дописал третий способ с макросом разделения слипшихся слов - пользуйтесь.
26.12.2014 13:18:50
здравствуйте! Помогите пжл с написанием макроса, вставляющего пробелы в ячейку со слипшимся текстом, выслали отчет с фиотипа ИвановИванИванович, не могу найти готовый макрос
27.12.2014 23:40:49
Яна, специально для вас дописал третий способ в статью - посмотрите ;)
31.12.2014 14:52:39
Спасибо ОГРОМНОЕ! С наступающим!
26.04.2015 23:45:52
А вот возможно ли разделить и размножить строку. Всю голову сломал )))
Например, дано
АРТИКУЛНаименование
3928797Блок цилиндров
5261376Вал коленчатый  
3970366/ 3907824/ 3283179Вал распределительный
Надо сделать вот так:
АРТИКУЛНаименование
3928797Блок цилиндров
5261376Вал коленчатый  
3970366Вал распределительный
3907824Вал распределительный
3283179Вал распределительный
27.04.2015 09:35:51
Такое только макросом :o
20.05.2015 07:16:41
Николай, скажите пожалуйста, можно ли выделить часть выдаваемого значения другим шрифтом или жирным шрифтом?
Пример формулы: =ЕСЛИ(AQ10=0;0;ПОДСТАВИТЬ(СЦЕПИТЬ(AG10;" ";AH10;", ";AK10;"х";AL10;"; ";AM10;AN10;"; ";ЕСЛИ(AJ10="V";"леска";0);", ";AO10;" / ";AP10;", ";AQ10;" шт.";);"0,";"";))
допустим результат, который получается по этой формуле: бязь красная, 1300х1400; П1000; леска, сверление / створочный, 1 шт.
нужно чтобы значение 1300х1400 было жирным шрифтом.
Заранее спасибо
20.05.2015 14:46:40
Такое только макросом. Функции или условное форматирование не могут менять часть текста ячейки, к сожалению :(
25.05.2015 05:44:13
Спасибо, Николай (:
29.05.2015 08:14:58
Добрый день.
Как быть в случае, если данные вставлены ссылками из других файлов excel, тогда стандартная функция деления воспринимает ссылки, не значения.
Возможно ли это сделать минуя копирование и вставку значений?
И как можно автоматизировать деление на столбцы?
29.05.2015 12:56:47
Сначала заменять формулы на значения, а потом уже делить, однозначно.
Что вы понимаете под словом "автоматизировать"? Делать макросом?
29.05.2015 13:20:50
Да с помощью макросов, если без них нельзя, в идеале на мой конкретный случай нужен следующий сценарий: ссылки копируются в значения->удаляются "лишние символы"->деление по столбцам с фиксированной шириной.
29.05.2015 15:10:31
Видел Ваш урок по преобразованию в значения макросом. Для одной задачи использую его.
Благодарю. Добра Вам.
29.05.2015 10:59:42
Подскажите, включил разделение по столбцам, как в первом варианте, и теперь у меня в документе все вставки разделяются по столбцам. Как это отключить?
29.05.2015 12:57:18
Какие "вставки"? Поподробнее, пожалуйста.
29.05.2015 13:26:11
Николай спасибо за ответ. Я уже разобрался. Вставка была - копия из браузера таблицы из 2-х столбцов, с большим количеством данных. В первом столбце текст (по несколько слов в ячейке), а во втором цифры. Решение: Работа с данными - Текст по столбцам - ставим метку на "с разделителями" - Далее - оставляем галочку только в поле "знак табуляции" - Готово.

Но все равно спасибо за внимание))
17.06.2015 09:23:56
Недаром программеры едят свой хлеб :)
27.06.2015 10:02:04
Хотел бы узнать можно ли по описанию (текст не всегда находится в одном порядке) какого либо предмета разделить его по пареметрам?
27.06.2015 10:15:33
описаниеТовартип Мошность двигателя Цена ТПР
газонокосилка электрическая 2000 W  акумлятором, лезвии шнур 20м б\у 2000рГазонокосилкаЭлектрическая2000W2000Акумлятор/Шнур
2000 W электрическая гозонокосилка с акумлятором 20м шнур б/у есть дополнительные лезвии  2500рГазонокосилкаЭлектрическая2000W2500Акумлятор/Шнур
Пила электрическая 5000 W 6000рПилаЭлектрическая5000W6000Шнур
бензопила 3.8 куб двиг 200р Пила Бензиновый3,8 куб200Бак
03.07.2015 10:18:15
А как программа должна отличить цену от мощности, например? Или от объема двигателя? Тут и человек не всегда разберется.
03.07.2015 07:43:29
А как при помощи текста по столбцам, удалить все предыдущие "разделения" и оставить последний.
Объясню: мне посоветовали вытащить последнюю строку из текста при помощи данной функции, по формуле никак не получается
Вот ссылка: http://planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=66908&MID=560789#message560789
03.07.2015 10:16:54
Санжар, вам же кучу способов на форуме накидали для решения этой проблемы :)
29.07.2015 17:33:56
Подскажите необходимо разбить текст по столбцам пример
5-dr SUV   07-15 (USA)
результат
5-drSUVиюл.15(USA)
одну из ячеек переводит в формат даты  
22.09.2015 13:34:59
Для столбца похожего на даты установите на третьем шаге текстовый формат.
09.08.2015 19:48:59
Здравствуйте.
По поводу функции разделения текста.
У меня офис 2013 (позаимствованный) и на функцию ругается.
Подскажите, пожалуйста, лекарство.
Спасибо.

PS функция в файле с примером работает без капризов.
21.09.2015 23:16:28
Здравствуйте!

А можно, используя 2й способ разделения текста на части, где приведен пример с адресом, чтобы после разделения в ячейках после слов сохранялись запятые?
Подскажите, пожалуйста, очень нужно.  
20.12.2016 13:13:17
Здравствуйте, надеюсь вопрос по теме. Есть текст с данными

Ma2 = 400 Nm; Mn2 = 200 Nm; n2 = 118 rpm;
v = 0 - 1,6 m/s;
max. load 200% (10s)
100% continuous
Roll Dia. 260mm


Нужно вытащить значения из текста и скопировать в определенные ячейки. Подскажите пожалуйста, каким инструментом воспользоваться?
Нужно будет вероятно дополнительно "объяснить" EXCELL учитывать возможные пробелы

Заранее благодарю!
Павел
23.03.2017 19:32:28
здравствуйте. в моем случае есть текст Иванов1987, Петров1960, Сидоров 1980....
или
1,234,567,890,123,456,789,01

как в первом случае отделить слова от цифр а во втором разделить дроби до сотых (например 1,23 4,56 7,89 0,12 3,45 6,78 9,01)

спасибо
26.03.2017 20:52:28
Для фамилий можно использовать макрофункции типа GetText и GetNumbers из надстройки PLEX.
Для второго случая (если до запятой всегда одна цифра, а после - две) можно использовать Текст по столбцам.
19.04.2017 10:45:18
есть телефоны
+7 (909) 010 20 50
89179766539
79622529035
89602861996
79054839128
79606388893
79524418763
+7 (924) 820-65-25
79502203287
Надо там где начинается на 8 и на 7 переделать на +7
21.08.2017 00:38:40
Просто спасибо что вы есть !!! Я бывал на многих форумах , на многих сайтах, но ваш сайт мне больше всего по душе .
Желаю удачи во всем !
21.08.2017 10:39:50
Спасибо на добром слове :) Рад, что смог помочь :)
Хочу поддержать предыдущего комментатора! Только у вас можно найти решение экселевских проблем. Я всегда вас всем рекомендую. Спасибо вам! и удачи в этом проекте!