Делим слипшийся текст на части
Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:
- ФИО в одном столбце (а надо - в трех отдельных, чтобы удобнее было сортировать и фильтровать)
- полное описание товара в одном столбце (а надо - отдельный столбец под фирму-изготовителя, отдельный - под модель для построения, например, сводной таблицы)
- весь адрес в одном столбце (а надо - отдельно индекс, отдельно - город, отдельно - улица и дом)
- и т.д.
Поехали..
Способ 1. Текст по столбцам
Выделите ячейки, которые будем делить и выберите в меню Данные - Текст по столбцам (Data - Text to columns). Появится окно Мастера разбора текстов:
На первом шаге Мастера выбираем формат нашего текста. Или это текст, в котором какой-либо символ отделяет друг от друга содержимое наших будущих отдельных столбцов (с разделителями) или в тексте с помощью пробелов имитируются столбцы одинаковой ширины (фиксированная ширина).
На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) - необходимо указать какой именно символ является разделителем:
Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.
Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании "Иванов, Манн и Фарбер") не делился по запятой
внутри названия.
И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:
- общий - оставит данные как есть - подходит в большинстве случаев
- дата - необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
- текстовый - этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:
Кнопка Подробнее (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 - порядковый номер извлекаемого фрагмента
Например:
Способ 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
Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:
Ссылки по теме
- Деление текста при помощи готовой функции надстройки PLEX
- Что такое макросы, куда вставлять код макроса, как их использовать
Или символ амперсанда: =A1&" "&B1
Если в А1 будет "Наталья", а в В1 "Петровна", то на выходе такая формула выдаст "Наталья Петровна"
а уже после этого можно мастером сделать то, для чего все мы тут собрались...
Коллеги, а подскажите - как работает в этом окне Ограничитель строк?
Наверняка какая-то полезная весчь, только ума не приложу - как использовать...
Скажите можно ли разделить корректно при помощи мастера текстов вот этот текст:
01Монитор Acer V223WEbd s/n ETLC30811701502E314275
разделить так:
1 ячейка 01Монитор Acer V223WEbd
2.ячейка ETLC30811701502E314275
1 ячейка НЖМД 2.0TbSeagate ST32000644NS
2 ячейка 9WM0GAK4
У меня получается всё по разным ячейкам
не могу найти формулу для превращение Фамилии Имя Отчества в Фамилию И.О.
Фамилия Имя Отчество в ОДНОЙ ячейке.
Создала такое:
=СЦЕПИТЬ(ЛЕВСИМВ(СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);1));ПСТР(СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);1)+1;1);".";ПСТР(СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);НАЙТИ(" ";СЖПРОБЕЛЫ(D6);1)+1)+1;1);"." )
есть возможность как-то упростить формулу?
Воспользовался Вашим макросом из
Получив результат, решил пойти дальше, разбив пути к файлам по столбцам. Разбить-то разбил без проблем. Но т.к. в папке есть вложенные папки, а в них могут быть и ещё, т.е. пути к конечным файлам имеют разную длину. Хотелось так: 1-й столбец - основная папка, 2-й столбец - папка первого вложения, 3-й - папка следующего вложения,... последний столбец - собственно файл. А в результате получилось не совсем так, 1-й столбец конечно заполнился, а вот с остальными пошла чехарда. В качестве разделителя выбрал слеш \.
Что можно предпринять?
300-9179- 00147
Почитал посмотрел, сделал, получилось!
Вопрос возник:
Например: 123 абв один а
Делим: с помощью VBA по пробелам, выбираем что отобразить.
Вопрос: Мне надо отобразить все после первого пробела. Как сделать?
P.S. Извините если кто то задавал этот вопрос, я посмотрел, вроде нет.
Если да, то отрезать функцией "Правсимв" до 4 -й позиции.
Ох, не посмотрел на дату))
"=ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(" ";A1;1))"
=) спасибо все равно)
=ПРАВСИМВ(текст;ДЛСТР(текст)-НАЙТИ("*";ПОДСТАВИТЬ(текст;" ";"*";ДЛСТР(текст)-ДЛСТР(ПОДСТАВИТЬ(текст;" ";"")))))
где текст - ссылка на ячейку с ФИО.
Например, дано
Пример формулы: =ЕСЛИ(AQ10=0;0;ПОДСТАВИТЬ(СЦЕПИТЬ(AG10;" ";AH10;", ";AK10;"х";AL10;"; ";AM10;AN10;"; ";ЕСЛИ(AJ10="V";"леска";0);", ";AO10;" / ";AP10;", ";AQ10;" шт.";);"0,";"";))
допустим результат, который получается по этой формуле: бязь красная, 1300х1400; П1000; леска, сверление / створочный, 1 шт.
нужно чтобы значение 1300х1400 было жирным шрифтом.
Заранее спасибо
Как быть в случае, если данные вставлены ссылками из других файлов excel, тогда стандартная функция деления воспринимает ссылки, не значения.
Возможно ли это сделать минуя копирование и вставку значений?
И как можно автоматизировать деление на столбцы?
Что вы понимаете под словом "автоматизировать"? Делать макросом?
Благодарю. Добра Вам.
Но все равно спасибо за внимание))
Объясню: мне посоветовали вытащить последнюю строку из текста при помощи данной функции, по формуле никак не получается
Вот ссылка:
По поводу функции разделения текста.
У меня офис 2013 (позаимствованный) и на функцию ругается.
Подскажите, пожалуйста, лекарство.
Спасибо.
PS функция в файле с примером работает без капризов.
А можно, используя 2й способ разделения текста на части, где приведен пример с адресом, чтобы после разделения в ячейках после слов сохранялись запятые?
Подскажите, пожалуйста, очень нужно.
описание
скачать
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 учитывать возможные пробелы
Заранее благодарю!
Павел
или
1,234,567,890,123,456,789,01
как в первом случае отделить слова от цифр а во втором разделить дроби до сотых (например 1,23 4,56 7,89 0,12 3,45 6,78 9,01)
спасибо
Для второго случая (если до запятой всегда одна цифра, а после - две) можно использовать .
Нарпимер:
Было 94/2 нужно 94
Было 107А3 нужно 107
Желаю удачи во всем !
А почему формула CutWords не хочет работать с латиницей, не делит?
Есть набор цифр в текстовом формате , например 1234567812345678 (всего 16 цифр). Надо каким то образом разделить эти цифры на группы по 4 цифры (1234 5678 1234 5678). Возможно такое сделать?
Огромная Вам благодарность !!!!!
Рис зерновой Краснодар12.05.2019
Мука ржаная Алтай03.02.2018
Сахар песок Елец16.06.2020
Чтоб было:
Рис зерновой Краснодар 12.05.2019
Мука ржаная Алтай 03.02.2018
Сахар песок Елец 16.06.2020
Не работает! Почему?
Также то, что заключено в кавычки как текст, по моему и воспринимается как текст и формула ищет именно знаки вопроса.
Тут тоже засада: кавычки служебный символ! Но я выкрутился! Может кому пригодится: =ЕСЛИ(ЕОШИБКА(НАЙТИ(СИМВОЛ(34);H4;1));"физич";"юридич")
у меня катая не простая ситуация выгрузка на эксел выгрузился таблица в таком виде:
Заранее спасибо всем!
как в экселе из предложения выделить все согласные буквы и поместить их в одну строчку?
Спасибо.
Подскажите, пожалуйста, как разделить информацию в ячейки, так чтобы нули перед цифрами не исчезли, а остался текстовый формат. Пример: 000.11111.09111.020С90. Нужно чтобы в ячейке при разделении остались цифры 09111 и 020С90.
например:
иными словами число перед буквами но после знака "_" потом буквы их не больше 3 бывает итд.