Разделением текста по столбцам excel

In this guide, we’re going to show you how to split text in Excel by a delimiter.

Download Workbook

We have a sample data which contains concatenated values separated by “|” characters. It is important that the data includes a specific delimiter character between each chunk of data to make splitting text easier.

Text to Columns feature for splitting text

When splitting text in Excel, the Text to Columns is one of the most common methods. You can use the Text to Columns feature with all versions of Excel. This feature can split text by a specific character count or delimiter.

1. Start with selecting your data. You can use more than one cell in a column.
2. Click Data > Text to Columns in the Ribbon.
3. On the first step of the wizard, you have 2 options to choose from — these are slicing methods. Since our data in this example is split by delimiters, our choice is going to be Delimited.
4. Click Next to continue
5. Select the delimiters suitable to your data or choose a character length and click Next.
6. Choose corresponding data types for the columns and the destination cell. Please note that if the destination cell is the same cell as where your data is, the original data will be overwritten.
7. Click Finish to see the outcome.

Using formulas to split text

Excel has a variety of text formulas that you can use to locate delimiters and parse data. When using formulas to do so, Excel automatically updates the parsed values when the source is updated.

The formula used in this example uses Microsoft 365’s dynamic array feature, which allows you to populate multiple cells without using array formulas. If you can see the SEQUENCE formula, you can use this method.

Syntax

How it works

The formula replaces each separator character with space characters first. (see SUBSTITUTE) The number of space characters will be equal to the original string’s character count, which is enough number of spaces to separate each string block (see REPT and LEN).

The SEQUENCE function generates an array of numbers starting with 1, up to the number of maximum columns. Multiplying these sequential numbers with the length of the original string returns character numbers indicating the start of each block.

This approach uses the MID function to parse each string block with given start character number and the number of characters to return. Since the separators are replaced with space characters, each parsed block includes these spaces around the actual string. The TRIM function then removes these spaces.

Flash Fill

The Flash Fill is an Excel tool which can detect the pattern when entering data. A common example is to separate or merge first name and last names. Let’s say column A contains the first name — last name combinations. If you type in the corresponding first name in the same row for column B, Excel shows you a preview for the rest of the column.

You can see the same behavior with strings using delimiters to separate data. Just select a cell in the adjacent column and start typing. Occasionally Excel will display a preview. If not, press Ctrl + E like below to split your text.

Power Query

Power Query is a powerful feature, not only for splitting text, but for data management in general. Power Query comes with its own text splitting tool which allows you to split text in multiple ways, like by delimiters, number of characters, or case of letters.

1. Select the cells containing the text.
2. Click Data > From Sheet. If the data is not in an Excel Table, Excel converts it into an Excel Table first.
3. Once the Power Query window is open, find the Split Column under the Transform tab and click to see the options.
4. Select the approach that fits your data layout. The data in our example is using By Delimiter since the data is separated by “|”.
5. Power Query will show the delimiter character. If you are not seeing the expected delimiter, choose from the list or enter it yourself.
6. Click OK to split text.
7. If your data includes headers in its first row, like our example does, click Transform > Use First Row as Headers to keep them as headers.
8. One you satisfied with the result, click the Home > Close & Load button to move the split data into your workbook.

VBA

VBA is the last text splitting option we want to show in this article. You can use VBA in two ways to split text:

1. By calling the Text to Columns feature using VBA,
2. Using VBA’s Split function to create array of sub-strings and using a loop to distribute the sub-strings into adjacent cells in the same row.

Text to Columns by VBA

The following code can split data from selected cells into the adjacent columns. Note that each supported delimiter is listed as an argument which you can enable or disable by giving them either True or False. Briefly, True means that you want to set that argument as a delimiter and False means ignore that character.

An easy way to generate a VBA code to split text is to record a macro. Start a recording section using the Record Macro button in the Developer tab, and use the Text to Columns feature. Once the recording is stopped, Excel will save the code for what you did during recording.

The code:

Sub VBATextToColumns_Multiple()
Dim MyRange As Range
Set MyRange = Selection ‘To work on a static range replace Selection via Range object, e.g., Range(“B6:B12”)

MyRange.TextToColumns _
Destination:=MyRange(1, 1).Offset(, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
SemiColon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=»|»
End Sub

Split Function

Split function can be useful if you want to keep the split blocks in an array. You can only use this method for splitting because of the single delimiter constraint. The following code loops through each cell in a selected column, splits and stores text by the delimiter “|”, and uses another loop to populate the values in the array on cells. The final EntireColumn.AutoFit command adjusts the column widths.

The code:

Sub SplitText()
Dim StringArray() As String, Cell As Range, i As Integer
For Each Cell In Selection ‘To work on a static range replace Selection via Range object, e.g., Range(“B6:B12”)
StringArray = Split(Cell, «|») ‘Change the delimiter with a character suits your data
For i = 0 To UBound(StringArray)
Cell.Offset(, i + 1) = StringArray(i)
Cell.Offset(, i + 1).EntireColumn.AutoFit ‘This is for column width and optional.
Next i
Next
End Sub

Помогла ли Вам статья?

Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы

​Смотрите также​ в позиции 6​Примечание:​Извлекаем столицу штата:​ имени запятой, и​Sally​LEFT​ по нескольким. На​, и «Мгновенное заполнение»​ существует ли в​ в предыдущем примере,​ следующим способом.​ данных для каждого​ разбивались, а рассматривались​(Мастер распределения текста​Last Name​«Текст по столбцам» позволяет​В этой статье Вы​ (шестой символ слева),​

​ Диапазон, содержащий столбец, который​=RIGHT(A2,LEN(A2)-(SEARCH(«,»,A2,SEARCH(«,»,A2)+1)+1))​ отчеством, находящимся в​K.​(ЛЕВСИМВ) и​ рисунке ниже показано,​ автоматически заполнило остальные​ них какая-либо закономерность.​ а затем нажмите​К примеру, есть список​ конкретного столбца, выделите​ как цельные значения.​ по столбцам). На​,​ разбивать значения ячеек,​ найдёте несколько способов,​ а в строке​ вы хотите разделить​=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«,»;A2;ПОИСК(«,»;A2)+1)+1))​ конце:​Brooke​

• ​RIGHT​ какого результата мы​ ячейки именами из​
• ​ Как только «Мгновенное​Finish​
• ​ товаров с ID​ его, кликнув по​ Например, если Вы​
• ​ первом шаге мастера​Country​ отделённые разделителями, или​

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

​ как разбить ячейки​​ с «Williams, Janet»,​​ может включать любое​А вот пример реальных​A​Извлекаем имя:​(ПРАВСИМВ) – возвращает​ пытаемся достичь:​ столбца​

​ заполнение» распознает Ваши​(Готово).​ и наименованием, причем​ нему в области​ выберите в качестве​ Вы выбираете формат​и​ выделять данные фиксированной​ или целые столбцы​

• ​ – 9.​ количество строк, но​
• ​ данных из Excel​B​

Разбиваем текстовые данные с разделителями по столбцам в Excel

​=LEFT(A2,FIND(» «,A2,1)-1)​ левую или правую​Вы легко сможете разбить​A​ действия и вычислит​Если Вы объединили несколько​​ идентификатор товара –​​Data preview​ разделителя запятую, а​ данных. Так как​

​Arrival Date​ ширины (когда все​ в Excel 2010​Чтобы вывести в другую​ он может содержать​​ 2010. Данные из​​C​​=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)​​ часть текста из​​ такие имена на​​. Если вы довольны​​ закономерность, Excel предложит​​ ячеек на листе​ это 9 символов,​​(Образец разбора данных),​​ в качестве ограничителя​

1. ​ записи разделены пробелами​.Если кто-то забыл, я​ значения содержат определённое​ и 2013. Приведённые​ ячейку только имя,​ не более одного​ первого столбца разбиты​D​Извлекаем отчество:​ заданной ячейки соответственно.​ два столбца при​ результатом, просто нажмите​ вариант, и последовательность​ Excel и теперь​ которые стоят перед​​ а затем установите​​ строк – кавычки​​ и запятыми, мы​​ напомню быстрый способ​ количество символов). Давайте​ примеры и скриншоты​​ используйте формулу ниже:​​ столбца. Это важные​​ на три отдельных​​1​​=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«​​ Синтаксис формулы:​ помощи следующих формул:​Enter​ записей в новом​ хотите вновь разбить​ наименованием этого товара:​​ желаемый формат в​​ («), тогда любые​ выбираем формат​ вставить сразу несколько​ рассмотрим эти варианты​ иллюстрируют работу с​=RIGHT(A2,LEN(A2)-FIND(«,»,A2)-1)​ из них достаточно​ столбца:​Полное имя​ «,A2,1)+1))​=LEFT(text,[num_chars])​Извлекаем имя (столбец First​​, и весь столбец​​ столбце появится буквально​

   

   ​ их по отдельным​Вот что Вам нужно​ разделе​ слова, заключённые в​Delimited​ столбцов на лист​

   

​ подробнее:​​ инструментами «Текст по​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(«,»;A2)-1)​ пустыми столбцами справа​Урок подготовлен для Вас​Имя​=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«​=ЛЕВСИМВ(текст;[количество_знаков])​ name):​ будет заполнен именами.​ за мгновение.​ столбцам, откройте вкладку​ сделать, чтобы разбить​Column data format​ кавычки (например, «California,​

1. ​(С разделителями). Вариант​ Excel. Для этого​Как разбить текст с​​ столбцам» и «Мгновенное​​Пояснение:​​ от выбранных столбец,​​ командой сайта office-guru.ru​Отчество​​ «;A2;1)+1))​​В качестве аргументов указываем:​
2. ​=LEFT(A2,SEARCH(» «,A2,1)-1)​​ Очень умный инструмент,​Таким образом, при помощи​​Home​ такой столбец на​(Формат данных столбца).​ USA»), будут помещены​Fixed width​ выберите столбец​ разделителями по столбцам​ заполнение», кроме этого​​Чтобы найти положение запятой,​​ который будет предотвратить​​Источник: https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/​​Фамилия​Извлекаем фамилию:​ какой текст взять​=ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)-1)​​ не правда ли?​​ этого инструмента Вы​
3. ​(Главная) и в​ два:​На этом же шаге​ в одну ячейку.​
   • ​(Фиксированной ширины) будет​​Status​Как выделить текстовые данные​ Вы увидите подборку​ используется функция​ перезапись по данные,​​Перевел: Антон Андронов​​2​​=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))​ и сколько символов​Извлекаем фамилию (столбец Last​​Если «Мгновенное заполнение» включено,​​ можете взять какую-то​ группе команд​​Запустите инструмент​​ мастера Вы можете​​ Если же в​​ рассмотрен чуть позже.​, кликнув по его​ фиксированной величины​​ формул для разделения​FIND​​ которые распространяться данные​Автор: Антон Андронов​White, David Mark​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))​ извлечь. В следующем​ name):​ но не предлагает​
   • ​ часть данных, находящихся​​Alignment​Text to Columns​ выбрать, в какой​ качестве ограничителя строк​ Если все готово,​ заголовку, и, удерживая​Предположим, есть список участников,​ имён, текстовых и​(НАЙТИ) — позиция​ в любой смежных​Примечание:​David​Функция​ примере формула будет​=RIGHT(A2,LEN(A2)-SEARCH(» «,A2,1))​ никаких вариантов, которые​ в одном или​(Выравнивание) нажмите маленькую​(Текст по столбцам),​ столбец поместить разделённые​ установить значение​ жмите​ нажатой левую кнопку​ приглашённых на конференцию​​ числовых значений. Этот​​ 6.​ столбцах. При необходимости​Мы стараемся как​Mark​MID​

   ​ извлекать левую часть​=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(» «;A2;1))​​ соответствуют определённому шаблону,​​ нескольких столбцах, и​ чёрную стрелку рядом​​ как мы это​​ данные. Для этого​None​Next​ мыши, протащите указатель​ или какое-то другое​ урок поможет Вам​

4. ​Чтобы получить длину строки,​ вставьте количество пустые​ можно оперативнее обеспечивать​White​(ПСТР) – извлекает​​ текста из ячейки​​Для тех, кому интересно,​ Вы можете запустить​ ввести их в​ с кнопкой​ делали в предыдущем​ кликните по иконке​(Нет), тогда слово​(Далее), чтобы продолжить.​ вправо, чтобы выделить​​ мероприятие. На рисунке​​ выбрать наилучший метод​ используется функция​ столбцы, будет достаточно​ вас актуальными справочными​Извлекаем имя:​ часть текстовой строки​​A2​​ что означают эти​ этот инструмент вручную​ новый столбец. Думаю,​Merge & Center​ примере. На первом​ выбора диапазона (в​ «California» будет помещено​На следующем шаге определяем​​ нужное количество столбцов​​ ниже видно, что​ разбиения данных в​LEN​ для хранения каждого​​ материалами на вашем​​=MID(A2,SEARCH(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«​

   

   ​ (то есть заданное​вплоть до позиции​ формулы, я попробую​ на вкладке​ Вы лучше поймёте​(Объединить и поместить​ шаге мастера выберите​ терминах Microsoft эта​ в один столбец,​​ разделители, которые содержатся​​ (сколько хотите вставить).​​ в столбце​​ Excel.​(ДЛСТР) — 11​ составные части распределенных​ языке. Эта страница​ «,A2,1)+1))​ количество символов). Синтаксис:​ первого найденного пробела.​ объяснить более подробно.​Data​ о чём я​ в центре). Далее​ параметр​ иконка называется​ а «USA» –​

​ в данных, и​​ Затем кликните правой​Participant​Говоря в общем, необходимость​ символов.​​ данных.​​ переведена автоматически, поэтому​=ПСТР(A2;ПОИСК(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«​=MID(text,start_num,num_chars)​​=LEFT(A2,SEARCH(» «,A2)-1)​​SEARCH​(Данные) >​​ говорю из следующего​​ из выпадающего списка​

1. ​Fixed width​​Свернуть диалоговое окно​​ в другой.​

Разбиваем текст фиксированной ширины по нескольким столбцам

​ ограничитель строк.​ кнопкой мыши по​(Участник) перечислены имена​ разбить ячейки в​Формула сводится к:​Выделите ячейку, диапазон или​ ее текст может​

​ «;A2;1)+1))​=ПСТР(текст;начальная_позиция;количество_знаков)​=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)​(ПОИСК) или​Flash Fill​ примера.​ выберите​

​(Фиксированной ширины) и​) справа от поля​В нижней части диалогового​Настраиваем разделители​

1. ​ выделенной области и​​ участников, государство и​​ Excel может возникнуть​=ПРАВСИМВ(A2;11-6-1)​ весь столбец, где​ содержать неточности и​Извлекаем отчество:​В качестве аргументов функции​​LEN​​FIND​(Мгновенное заполнение) или​​Первым делом, убедитесь, что​​Unmerge Cells​
2. ​ нажмите​​Destination​​ окна находится область​. Если данные разделены​ в контекстном меню​ ожидаемая дата прибытия:​ в двух случаях:​.​ содержатся текстовые значения,​ грамматические ошибки. Для​=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))​ указываем: какой текст​(ДЛСТР) – считает​(НАЙТИ) – это​ нажав сочетание клавиш​ инструмент «Мгновенное заполнение»​(Отменить объединение ячеек).​Next​(Поместить в) и​Data preview​ одним или несколькими​ выберите команду​Необходимо разбить этот текст​ ​ Во-первых, при импорте​Выражение​ которые требуется разделить.​ нас важно, чтобы​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))​ взять, позицию символа,​ длину строки, то​
3. ​ абсолютно идентичные функции,​Ctrl+E​ включен. Вы найдёте​Таким образом объединение ячеек​(Далее).​ выберите крайний левый​(Образец разбора данных).​​ разделителями, то нужно​​Insert​

Разбиваем объединённые ячейки в Excel

​ на отдельные столбцы,​ информации из какой-либо​=ПРАВСИМВ(A2;4)​На вкладке​ эта статья была​Извлекаем фамилию:​​ с которого нужно​​ есть количество символов​ которые выполняют поиск​​.​​ этот параметр на​ будет отменено, но​В разделе​​ столбец из тех,​​ Прежде чем нажать​ выбрать все подходящие​(Вставить).​ чтобы таблица имела​​ внешней базы данных​​извлекает 4 символа​

​Данные​ вам полезна. Просим​=LEFT(A2,FIND(» «,A2,1)-2)​ начать, и сколько​ в заданной ячейке.​ позиции определенной текстовой​Существуют формулы, которые могут​ вкладке​ удовольствие от результата​​Data preview​​ в которые Вы​Next​ варианты в разделе​Результат будет примерно таким,​ следующие данные (слева​

Разделяем данные на несколько столбцов в Excel 2013 при помощи мгновенного заполнения

​ или с веб-страницы.​ справа и выводит​в группе​ вас уделить пару​=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-2)​​ символов извлечь.​​ Синтаксис формулы:​ строки в заданной​ быть очень полезны,​File​ будет испорчено тем,​

​(Образец разбора данных)​ хотите поместить разделённые​(Далее) будет разумным​D​ что Вы видите​ направо):​ При таком импорте​ желаемый результат —​Работа с данными​ секунд и сообщить,​Как Вы понимаете, эти​Вы можете использовать аналогичные​=LEN(text)​ ячейке. Синтаксис формулы:​ когда возникает необходимость​(Файл) >​ что все данные​ настройте ширину столбцов.​ данные. К сожалению,​ пролистать это поле​

​elimiters​ на рисунке ниже​First Name​ все записи копируются​ «Mike».​нажмите кнопку​ помогла ли она​ формулы работают не​ формулы, чтобы разбить​=ДЛСТР(текст)​=SEARCH(find_text,within_text,[start_num])​ разбить ячейки или​

​Options​ останутся в левом​ Как видно на​ невозможно импортировать разделённые​ и убедиться, что​​(Символом-разделителем является) или​​ (новые столбцы вставлены​​(Имя),​​ в один столбец,​​Чтобы вывести в другую​​Текст по столбцам​​ вам, с помощью​​ только для разделения​ имена с суффиксами​

​Следующая формула считает количество​=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])​ столбцы с данными​(Параметры) >​ столбце. Думаю, Вы​ рисунке ниже, край​ данные на другой​ Excel правильно распределил​ ввести свой вариант​ слева от выделенных​Last Name​ а нужно, чтобы​ ячейку только фамилию,​.​ кнопок внизу страницы.​ имён в Excel.​ в конце:​ символов в ячейке​В качестве аргументов Вы​ в Excel. На​

​Advanced​ догадались, что нужно​ столбца символизирует вертикальная​​ лист или в​​ все данные по​ разделителя в поле​ столбцов):​(Фамилия),​​ они были помещены​​ используйте формулу ниже:​Следуйте инструкциям в​​ Для удобства также​​ Вы можете использовать​A​A2​ должны указать: что​

​ самом деле, следующих​(Дополнительно) >​ снова использовать функцию​ линия, и чтобы​ другую рабочую книгу,​ столбцам.​Other​​Примечание:​​Country​​ в разных столбцах.​​=LEFT(A2,FIND(«,»,A2)-1)​Столбцах мастера​​ приводим ссылку на​​ их для разбиения​

Как в Excel разбивать ячейки при помощи формул

​B​:​ нужно найти, где​ шести функций будет​Automatically Flash Fill​Text to Columns​ задать край следующего​ попытка сделать это​Осталось сделать всего две​(Другой).В нашем примере​​Если у Вас​​(Страна),​​ Во-вторых, при разбиении​​=ЛЕВСИМВ(A2;НАЙТИ(«,»;A2)-1)​​, чтобы указать способ​​ оригинал (на английском​​ любых данных из​​C​​=LEN(A2)​​ нужно искать, а​​ достаточно в большинстве​​(Автоматически выполнять мгновенное​(Текст по столбцам),​ столбца, просто кликните​ приведёт к сообщению​ вещи – выбрать​ мы выбираем​ нет столбцов, следующих​Arrival Date​

Пример 1

​ уже существующей таблицы,​Пояснение:​ разбиения текста на​ языке) .​ одного столбца по​D​=ДЛСТР(A2)​ также позицию символа,​ случаев –​

​ заполнение).​ чтобы разбить данные​ в нужном месте.​ об ошибке выбора​

• ​ формат данных и​Space​

  ​ непосредственно за тем,​
​(Ожидаемая дата прибытия)​

• ​ чтобы получить возможность​Чтобы найти положение запятой,​

  ​ разных столбцах.​
​Можно разделить содержимого ячейки​

​ нескольким. Например, следующие​1​Если имена в Вашей​ с которого следует​

​LEFT​​Теперь давайте посмотрим, как​​ из одного столбца​​ Двойной щелчок по​ конечной ссылки.​ указать, куда поместить​(Пробел) и​ что Вы хотите​ и​

​ качественнее настроить работу​
​ используется функция​

​Примечание:​ и распространение составные​ формулы Вы можете​Полное имя​ таблице содержат отчества​ начать поиск. В​(ЛЕВСИМВ),​ можно автоматически разбить​​ на два или​​ вертикальной линии удалит​​Совет:​​ разделённые ячейки.В разделе​Comma​ разбить, то необходимость​Status​​ фильтра, сортировку или​​FIND​ Для получения справки по​

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

​Если Вы не​
​Column data format​

​(Запятая), а также​​ в этом шаге​​(Статус).​​ для более детального​(НАЙТИ) — позиция​ заполнению все действия​ ячеек. Например, если​ текстовые данные, разделённые​

​Фамилия​
​ потребуются немного более​

​SEARCH(» «,A2,1)​(ПСТР),​ Итак, Вы включили​Если Вы уже обновились​ если Вам нужно​ хотите импортировать какой-то​(Формат данных столбца)​​ ставим галочку напротив​​ отпадает и его​Если в таблице есть​

​ анализа.​
​ 6.​

​ мастера читайте статью​​ лист содержит​ запятыми:​Суффикс​ сложные формулы с​или​

​RIGHT​
​ инструмент «Мгновенное заполнение»,​

​ до Excel 2013,​ переместить границу столбца​​ столбец (столбцы), который​​ Вы можете выбрать​

​ параметра​
​ можно пропустить. Главное​

​ хотя бы один​Разбиваем ячейки при помощи​Формула сводится к:​ Разделение текста по​Полное имя​A​​2​​ использованием функции​

Пример 2

​ПОИСК(» «;A2;1)​(ПРАВСИМВ),​ и начинаете вводить​ то можете воспользоваться​ в другое место,​ показан в области​

• ​ преимуществами нового инструмента​

  ​ просто перетащите вертикальную​
​Data preview​

• ​ для каждого столбца,​

  ​ one​ пустых столбцов должно​
​ столбца, который необходимо​ столбцам»​

• ​.​

  ​ преобразовать мастера текстов,​
​ на двух столбцов​

​C​​Robert​​(ПСТР).​ что мы хотим​(НАЙТИ),​ которые нужно поместить​

​ «​
​ линию мышью. На​

​(Образец разбора данных),​ в которые будут​(Считать последовательные разделители​ быть не меньше,​ разбить, тогда первым​Как разбить объединённые ячейки​

Пример 3

​Выражение​ или нажмите кнопку​ —​D​

• ​Справка​

  ​имени​
​1​

• ​Jr.​

  ​ использовать, когда имена,​ в ячейке​
​(ПОИСК) и​ По мере ввода​

• ​» и заставить Excel​

  ​ эти инструкции подробно​
​ и выберите вариант​

Пример 4

​ По умолчанию для​ поможет избежать лишнего​ на которое вы​ пустые столбцы, в​Разделяем данные в Excel​извлекает 5 знаков​в мастере разделения​

• ​ 2013 при помощи​

  ​ слева и дает​ текста столбцов.​
​Фамилии​Штат​

• ​=LEFT(A2,FIND(» «,A2,1)-1)​

  ​ содержат отчество или​
​и начнём поиск​

• ​(ДЛСТР). Далее в​

  ​ распознать шаблон в​
​ нашем случае –​

Пример 5

​ части диалогового окна​(Пропустить столбец) в​ формат​ когда между словами​Выделите столбец, который требуется​ полученные данные. Этот​ инструмента «Мгновенное заполнение»​ желаемый результат —​Этот пример показывает, как​столбцом.​Аббревиатура​=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)​ только один инициал​

• ​Столица​

  ​Извлекаем фамилию:​
​ отчества посередине.​

• ​Замечание:​

  ​ кратко объясню назначение​
​ как только он​

• ​ обнаружении определенной закономерности.​

  ​ товара содержит 9​
​Column data format​

​(Общий). Мы оставим​ более последовательных пробела.​ вкладку​ того, чтобы результаты​ (имен и других​Выделите диапазон​

​ несколько столбцов в​ ​
​2​
​=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«​

​A​

office-guru.ru

Распределение содержимого ячейки на соседние столбцы

​Если поиск начинается​​ каждой из этих​ его распознает, данные​Если Вы ещё не​ символов, устанавливаем линию​(Формат данных столбца).​ его без изменений​Настраиваем ограничитель строк​Data​ не были записаны​ текстовых данных)​B2:C2​ Excel.​Сведения об альтернативном способе​Alabama, AL, Montgomery​ «,A2,1)+1))​B​ с первого символа,​ функций и приведу​ автоматически будут вставлены​ знакомы с этой​ границы столбца на​

​Нажмите​ для первых трёх​. Этот параметр может​(Данные) >​ поверх уже существующих​​Инструмент «​​и протяните его​Проблема, с которой мы​ распределения текста по​​Alabama​​=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«​​C​​ Вы можете вообще​

​ примеры, которые Вы​​ в остальные ячейки.​

• ​ функцией, я попробую​ это значение, как​Finish​ столбцов, а для​ понадобиться, если в​Data Tools​

• ​ данных.В нашем примере​Текст по столбцам​ вниз, чтобы вставить​

​ имеем дело на​

​ столбцам см. в​​AL​ «;A2;1)+1))​D​ пропустить аргумент​ сможете использовать в​ Чтобы понять, как​ кратко объяснить её​ показано на рисунке​(Готово)!​ четвёртого столбца установим​ столбце, который Вы​(Работа с данными)​ сразу после столбца​» действительно очень удобен,​ формулу в остальные​ рисунке выше, в​ статье Разделение текста​Montgomery​Извлекаем суффикс:​1​

1. ​start_num​ своих книгах Excel.​ это работает, посмотрите​ суть. Этот инструмент​

2. ​ выше.​​Если данные состоят из​​ формат​​ разбиваете, содержатся какие-либо​​ >​​Participant​​ когда нужно разделить​

   

3. ​ ячейки.​​ том, что нужно​​ по столбцам с​Извлекаем название штата:​=RIGHT(A2,LEN(A2)-FIND(» «,A2,FIND(» «,A2,1)+1))​

   ​Полное имя​​(нач_позиция) в формуле​Самая распространённая ситуация, когда​ на рисунок ниже:​ анализирует данные, которые​На следующем шаге выберите​ текстовых или числовых​Data​​ значения, заключённые в​ ​Text to Columns​находится столбец​

support.office.com

Разделение строки на столбцы в Excel

​ данные из одного​Урок подготовлен для Вас​ объяснить Excel, в​ помощью функций.​

​=LEFT(A2,SEARCH(«,»,A2)-1)​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))​Имя​ и упростить её​ могут понадобится эти​Как видите, я ввёл​ Вы вводите на​ формат данных и​ значений с фиксированным​(Дата), что логично,​ кавычки или в​(Текст по столбцам).​Status​ столбца по нескольким​

1. ​ командой сайта office-guru.ru​ каком месте необходимо​Можно объединить ячейки, содержащие​

   ​=ЛЕВСИМВ(A2;ПОИСК(",";A2)-1)​
​А вот формулы, позволяющие​

   

   ​Отчество​

   • ​ до такого вида:​ формулы – это​​ только пару имён​​ рабочий лист, и​ укажите ячейки, куда​
   • ​ количеством символов, Вы​ ведь в этот​​ апострофы, и Вы​​Откроется диалоговое окно​, и мы собираемся​
   • ​ в Excel 2013,​​Источник: http://www.excel-easy.com/examples/separate-strings.html​​ разделить строку. В​
   • ​ функцию СЦЕП или​​Извлекаем аббревиатуру штата:​​ разбить имена с​Фамилия​=LEFT(A2,SEARCH(» «,A2)-1)​ необходимость разделить имена​
2. ​ в столбец​ пытается выяснить, откуда​ поместить результат, как​

   ​ можете разбить их​
​ столбец попадут даты​

   

   ​ хотите, чтобы такие​

   • ​Convert Text to Columns​ добавить между ними​​ 2010, 2007 или​​Перевел: Антон Андронов​ строке с текстом​
   • ​ функцию СЦЕПИТЬ.​​=MID(A2,SEARCH(«,»,A2)+2,SEARCH(«,»,A2,SEARCH(«,»,A2)+2)-SEARCH(«,»,A2)-2)​​ фамилией, стоящей впереди​
   • ​2​​=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)​​ из одного столбца​B​ они взялись и​ это было сделано​
3. ​ на несколько столбцов​​ прибытия.Чтобы изменить формат​​ участки текста не​ wizard​ новые столбцы​ 2003.​

​Автор: Антон Андронов​ «Smith, Mike» запятая​
​Выполните следующие действия.​
​=ПСТР(A2;ПОИСК(«,»;A2)+2;ПОИСК(«,»;A2;ПОИСК(«,»;A2)+2)-ПОИСК(«,»;A2)-2)​

​ и отделенной от​

office-guru.ru

​Sally K. Brooke​

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

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

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

Поехали..

Способ 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
• Что такое макросы, куда вставлять код макроса, как их использовать