Учебник по Excel: как разделить ячейки с текстом, числами и датами (разделение на несколько столбцов)

При работе в Excel иногда требуется разделить текст из одной ячейки на несколько для различных целей. Этот учебник состоит из трёх частей: разделение текстовых ячеек, числовых ячеек и ячеек с датой. В каждой части приведены разные примеры, которые помогут вам разобраться, как действовать при возникновении аналогичной задачи.

1 Разделение текстовых ячеек

В этой части собраны ситуации, с которыми вы столкнётесь при разделении текстовой ячейки на несколько столбцов, а также приведены соответствующие методы.

Пример №1. Разделить ячейки по запятой, пробелу или другим разделителям

Чтобы разделить текстовую ячейку на несколько столбцов по определённому разделителю, например, запятой, пробелу, дефису и т.д., вы можете воспользоваться одним из следующих способов.

Разделить ячейки по разделителю с помощью функции Разделить данные

Функция Разделить данные, встроенная в Excel, часто используется для разделения ячеек. Как показано на скриншоте ниже, чтобы разделить ячейки в столбце Text Strings по запятой, выполните следующие действия с помощью функции Разделить данные.

1. Выделите диапазон столбца, который хотите разделить по запятой, затем нажмите Данные > Разделить данные.

2. В диалоговом окне Мастер преобразования текста в столбцы — шаг1 из3 оставьте выбранным переключатель С разделителями и нажмите кнопку Далее.

3. В диалоговом окне Мастер преобразования текста в столбцы — шаг2 из3 укажите нужный разделитель (в данном случае я отмечаю только флажок Запятая), затем нажмите кнопку Далее.

4. В последнем диалоговом окне нажмите кнопку, чтобы выбрать ячейку для вывода разделённых текстов, затем нажмите Завершить кнопку.

После этого тексты в выбранном диапазоне будут разделены по запятой и размещены в разных столбцах, как показано ниже.

Разделить ячейки по разделителю с помощью формулы

Вы можете воспользоваться приведённой ниже формулой для разделения текста в ячейках по заданному разделителю в Excel.

Общая формула

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Аргументы

Далее примените эту формулу.

1. Как показано на скриншоте ниже, сначала создайте вспомогательную строку с числами1,2,3... в разных ячейках.

Примечание: Здесь1,2,3... обозначают первую, вторую и третью подстроку текстовой строки.

2. Выберите ячейку под ячейкой с числом1, скопируйте или введите приведённую ниже формулу и нажмите Enter, чтобы получить первую подстроку текстовой строки. Выделите полученную ячейку, перетащите маркер автозаполнения вправо и вниз, чтобы получить остальные подстроки. См. скриншот:

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

Примечание: В формуле “,” — это разделитель, используемый для разделения текстовой строки в B5. Вы можете изменить его по своему усмотрению.

Разделить ячейки по разделителю с помощью удобного инструмента

Здесь мы рекомендуем использовать функцию Разделить ячейки в Kutools для Excel, чтобы легко разделять ячейки на отдельные столбцы или строки по определённому разделителю в Excel.

1. После установки Kutools для Excel выберите диапазон, в котором хотите разделить текстовые строки, затем нажмите Kutools > Объединить и разделить > Разделить ячейки.

2. В диалоговом окне Разделить ячейки выполните следующие настройки.

3. В следующем диалоговом окне Разделить ячейки выберите ячейку для вывода подстрок и нажмите OK.

После этого подстроки будут разделены в разные столбцы или строки в соответствии с вашими настройками на шаге2.

Разделить данные:

Разделить на строки:

Пример №2. Разделить ячейки по определённой длине

Чтобы разделить текстовую строку по определённой длине, воспользуйтесь одним из следующих способов.

Разделить ячейки по определённой длине с помощью функции Разделить данные

Функция Разделить данные предлагает опцию Фиксированная ширина, которая позволяет разделять текстовые строки в выбранных ячейках по определённой длине в Excel.

Как показано на скриншоте ниже, чтобы разделить тексты в диапазоне B5:B9 на столбцы каждые3 символа, выполните следующие действия.

1. Выделите ячейки, в которых хотите разделить текстовые строки.

2. Нажмите Данные > Разделить данные.

3. В диалоговом окне Мастер преобразования текста в столбцы — шаг1 из3 выберите переключатель Фиксированная ширина и нажмите Далее.

4. Затем появится диалоговое окно Шаг2 из3. В разделе Предварительный просмотр данных щёлкните в нужном месте на оси, чтобы создать линию разрыва (линию со стрелкой). После создания всех линий разрыва нажмите кнопку Далее для продолжения.

В данном случае я создаю линии разрыва каждые3 символа в текстовой строке.

5. В последнем шаге мастера выберите ячейку для вывода разделённых текстов и нажмите кнопку Завершить.

Теперь текстовые строки в выбранных ячейках разделены каждые3 символа, как показано на скриншоте ниже.

Разделить ячейки по определённой длине с помощью удобного инструмента

Чтобы разделить ячейки по определённой длине в Excel, вы можете воспользоваться функцией Разделить ячейки в Kutools для Excel для быстрого выполнения задачи.

1. Выделите ячейки с текстовыми строками, которые хотите разделить по определённой длине, затем нажмите Kutools > Объединить и разделить > Разделить ячейки.

2. В диалоговом окне Разделить ячейки выполните следующие настройки.

3. В следующем диалоговом окне Разделить ячейки выберите ячейку для размещения разделённых текстов и нажмите OK.

После этого текстовые строки в выбранных ячейках будут разделены по определённой длине и размещены в разных столбцах.

Пример №3. Разделить ячейки по определённому слову

Как показано на скриншоте ниже, чтобы разделить текстовые строки в диапазоне B5:B9 по целому слову «sales», вы можете воспользоваться формулами, приведёнными в этом разделе.

Получить подстроку до определённого слова в ячейке

Применение формулы на основе функций LEFT и FIND поможет получить подстроку до определённого слова в текстовой строке.

Общая формула

=LEFT(A1,FIND("certain_word", A1)-1)

Аргументы

1. Выберите пустую ячейку, скопируйте или введите приведённую ниже формулу и нажмите Enter, чтобы получить подстроку до определённого слова. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам.

=LEFT(B5,FIND("sales",B5)-1)

Получить подстроку после слова в ячейке

После получения подстроки до слова используйте приведённую ниже формулу для получения подстроки после него.

Общая формула

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

Аргументы

1. Выберите пустую ячейку рядом с первой ячейкой с результатом (D5).

2. Скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

Теперь вы разделили текстовые строки на разные столбцы по целому слову.

Пример №4. Разделить ячейки по разрыву строки

В этом разделе показаны различные методы разделения текстовых ячеек по разрыву строки в Excel.

Разделить ячейки по разрыву строки с помощью функции Разделить данные

Функцию Разделить данные можно использовать для разделения ячеек по разрыву строки в Excel. Выполните следующие действия.

1. Выделите диапазон ячеек, в которых хотите разделить тексты по разрыву строки.

2. Нажмите Данные > Разделить данные.

3. В диалоговом окне Мастер преобразования текста в столбцы — шаг1 из3 выберите переключатель С разделителями и нажмите Далее;

4. В диалоговом окне Шаг2 из3 снимите все флажки разделителей, установите флажок Другое и нажмите сочетание клавиш Ctrl + J. В текстовом поле появится маленькая точка, а в окне Предварительный просмотр данных тексты будут разделены по разрывам строк. Нажмите кнопку Далее.

5. В последнем шаге мастера выберите ячейку назначения для вывода разделённых текстов и нажмите кнопку Завершить.

После этого тексты в выбранных ячейках будут разделены на разные столбцы по разрывам строк.

Разделить ячейки по разрыву строки с помощью формулы

Следующие формулы также помогут разделить ячейки по разрыву строки в Excel.

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

Получить подстроку до первого разрыва строки

Сначала можно применить формулу на основе функций LEFT и SEARCH для получения подстроки до первого разрыва строки в ячейке.

Общая формула

=LEFT(cell, SEARCH(CHAR(10),cell,1)-1)

Аргументы

1. Выберите пустую ячейку (в данном случае D5), скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить подстроки до первого разрыва строки в других ячейках.

=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

Получить подстроку между первым и вторым разрывами строки

Чтобы получить подстроку между первым и вторым разрывами строки в ячейке, воспользуйтесь следующей формулой.

Общая формула

=MID(cell,SEARCH(CHAR(10),cell)+1,SEARCH(CHAR(10),cell,SEARCH(CHAR(10),cell)+1)-SEARCH(CHAR(10),cell)-1)

Аргументы

1. Выберите ячейку (E5) рядом с D5, скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить подстроки между первым и вторым разрывами строки в других ячейках.

=MID(B5, SEARCH(CHAR(10),B5) +1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) -1)

Получить подстроку после второго разрыва строки

Первый шаг — получить подстроку после второго разрыва строки с помощью приведённой ниже формулы.

Общая формула

=RIGHT(cell,LEN(cell) - SEARCH(CHAR(10),cell, SEARCH(CHAR(10), cell) +1))

Аргументы

1. Выберите ячейку (F5 в данном случае), скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить подстроки после второго разрыва строки в других ячейках.

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) +1))

Разделить ячейки по разрыву строки с помощью VBA

В этом разделе представлен VBA-код, который поможет легко разделить тексты в выбранных ячейках по разрыву строки в Excel.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль. Затем скопируйте приведённый ниже VBA в окно кода.

VBA-код: разделить ячейки по разрыву строки в Excel

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. Нажмите клавишу F5 для запуска кода. Затем появится диалоговое окно Kutools для Excel, в котором нужно выбрать диапазон ячеек для разделения по разрыву строки и, наконец, нажать кнопку OK.

После этого тексты в выбранных ячейках будут сразу разделены на разные столбцы по разрывам строк.

Разделить ячейки по разрыву строки с помощью удобного инструмента

Здесь мы представляем удобный инструмент — функцию Разделить ячейки в Kutools для Excel. С помощью этого инструмента вы можете массово разделять тексты в выбранных ячейках по разрыву строки всего за несколько кликов.

1. Выделите диапазон ячеек, в которых хотите разделить тексты по разрыву строки.

2. Нажмите Kutools > Объединить и разделить > Разделить ячейки, чтобы включить функцию.

3. В диалоговом окне Разделить ячейки выполните следующие настройки.

4. В следующем всплывающем диалоговом окне Разделить ячейки выберите пустую ячейку для размещения разделённых текстов и нажмите OK.

После этого тексты в выбранных ячейках будут разделены на разные столбцы по разрывам строк, как показано на скриншоте ниже.

Пример №5. Разделить ячейки только по первому или последнему разделителю

Иногда может понадобиться разделить ячейку на две части только по первому или последнему разделителю, для этого воспользуйтесь приведёнными ниже формулами.

Разделить ячейки по первому разделителю с помощью формул

Как показано на скриншоте ниже, чтобы разделить каждую ячейку в диапазоне B5:B9 на две части по первому пробелу, нужно применить две формулы.

Разделить подстроку до первого пробела

Для разделения подстроки до первого пробела используйте формулу на основе функций LEFT и FIND.

Общая формула

=LEFT(cell,FIND("delimiter",cell)-1)

Аргументы

1. Выберите ячейку (в данном случае D5) для вывода подстроки, скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить подстроки других ячеек.

=LEFT(B5,FIND(" ",B5)-1)

Разделить подстроку после первого пробела

Затем примените приведённую ниже формулу для получения подстроки после первого пробела в ячейке.

Общая формула

=RIGHT(cell,LEN(cell)-FIND("delimiter",cell))

Аргументы

1. Скопируйте или введите приведённую ниже формулу в ячейку E5 и нажмите Enter, чтобы получить результат. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить подстроку после последнего пробела в других ячейках.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Примечание: В формулах вы можете изменить ссылку на ячейку и разделитель по своему усмотрению.

Разделить ячейки по последнему разделителю с помощью формул

Чтобы разделить тексты в диапазоне ячеек (B5:B9) на две части по последнему пробелу, как показано на скриншоте, воспользуйтесь двумя формулами, приведёнными в этом разделе.

Получить текст слева от последнего разделителя

Чтобы получить текст слева от последнего разделителя в ячейке, используйте приведённую ниже формулу.

Общая формула

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"delimiter","^",LEN(cell)-LEN(SUBSTITUTE(cell,"delimiter",""))))-1)

Аргументы

1. Выберите ячейку для вывода левой подстроки, скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам.

=LEFT(B5,SEARCH("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

Получить текст справа от последнего разделителя

Теперь нужно получить текст справа от последнего пробела в данном случае.

Общая формула

=TRIM(RIGHT(SUBSTITUTE(cell,"delimiter",REPT("delimiter",LEN(cell))),LEN(cell)))

Аргументы

1. Выберите ячейку для вывода правой подстроки, скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

Примечание: В формулах вы можете изменить ссылку на ячейку и разделитель по своему усмотрению.

Пример №6. Разделить ячейки по заглавной букве

В этом разделе представлена пользовательская функция для разделения слов в ячейках по заглавной букве.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем скопируйте приведённый ниже VBA-код в окно кода.

VBA-код: разделить ячейки по заглавной букве в Excel

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

3. Нажмите Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Выберите ячейку (в данном случае D5) для вывода слова слева от первой заглавной буквы, введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вправо, затем вниз, чтобы получить остальные слова.

=IFERROR(GetName($B5,COLUMNS($D:D)),"")

Примечание: В коде $B5 — это ячейка, которую вы будете разделять, $D:D — столбец ячейки с результатом. Измените их в соответствии с вашими данными.

Пример №7. Разделить имена в ячейках

Предположим, у вас есть лист с колонкой полных имён, и вы хотите разделить полное имя на отдельные столбцы, например, выделить имя и фамилию или имя, отчество и фамилию. В этом разделе приведены подробные шаги для решения этих задач.

Разделить полное имя на имя и фамилию

Как показано на скриншоте ниже, имя, отчество и фамилия разделены одним пробелом. Чтобы разделить только имя и фамилию из полного имени и поместить их в разные столбцы, воспользуйтесь одним из следующих способов.

1) Разделить полное имя на имя и фамилию с помощью формул

Для разделения полного имени на имя и фамилию необходимо применить две формулы отдельно.

Разделить имя из полного имени

Можно применить формулу на основе функций LEFT и SEARCH для выделения имени из полного имени.

Общая формула

=LEFT(cell, SEARCH(" ", cell) -1)

1. Выберите ячейку для вывода имени.

2. Скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы разделить имена из других полных имён.

=LEFT(B5, SEARCH(" ", B5) -1)

Теперь вы разделили все имена из полных имён в указанном диапазоне ячеек, далее примените приведённую ниже формулу для разделения фамилий.

Разделить фамилию из полного имени

Общая формула

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

1. Выберите ячейку рядом с ячейкой имени.

2. Скопируйте или введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить фамилии из других полных имён.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")))))

Примечание: В формулах B5 — это ячейка с полным именем, которую вы хотите разделить. Вы можете изменить её по необходимости.

2) Разделить полное имя на имя и фамилию с помощью удобного инструмента

Многие пользователи Excel считают формулы сложными для запоминания. В этом случае рекомендуем воспользоваться функцией Разделить имена в Kutools для Excel. С её помощью вы сможете легко разделить полное имя на имя и фамилию всего за несколько кликов.

1. Выделите диапазон ячеек с полными именами, нажмите Kutools > Объединить и разделить > Разделить имена.

2. В диалоговом окне Разделить имена отметьте только поля Имя и Фамилия в разделе Тип разделения, затем нажмите OK.

Совет: выбранный диапазон отображается в поле Диапазон разделения, вы можете изменить его по необходимости.

3. Затем появится ещё одно диалоговое окно Разделить имена, выберите ячейку назначения и нажмите OK.

После этого полные имена в выбранных ячейках будут массово разделены на имя и фамилию, как показано на скриншоте ниже.

Разделить полное имя на имя, отчество и фамилию

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

1) Разделить полное имя на имя, отчество и фамилию с помощью функции Разделить данные

Встроенная функция — Разделить данные поможет вам легко разделить полное имя на имя, отчество и фамилию в Excel.

Вы можете следовать шагам, описанным выше, чтобы применить функцию Разделить данные.

Примечание: В мастере шаг2 из3 отметьте только поле Пробел.

2) Разделить полное имя на имя, отчество и фамилию с помощью формул

Также можно воспользоваться формулами для разделения полного имени на имя, отчество и фамилию в Excel.

Общие формулы для разделения полного имени

Разделить имя

=LEFT(cell,SEARCH(" ", cell)-1)

Разделить отчество

=MID(cell, SEARCH(" ", cell) +1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

Разделить фамилию

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

Затем примените формулы в разных ячейках, чтобы получить имя, отчество и фамилию.

1. В ячейке D5 введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить имена из других полных имён.

=LEFT(B5,SEARCH(" ", B5)-1)

2. Введите приведённую ниже формулу в ячейку E5 и нажмите Enter, чтобы получить первое отчество. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные отчества.

=MID(B5, SEARCH(" ", B5) +1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. Чтобы получить фамилии, введите приведённую ниже формулу в ячейку F5 и нажмите Enter, затем выделите полученную ячейку и перетащите маркер автозаполнения по нужным ячейкам.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) Разделить полное имя на имя, отчество и фамилию с помощью удобного инструмента

Здесь представлена функция Разделить имена в Kutools для Excel, которая позволяет разделить полное имя на имя, отчество и фамилию сразу всего за несколько кликов.

1. Выделите ячейки с полными именами, которые хотите разделить, затем нажмите Kutools > Объединить и разделить > Разделить имена.

2. В диалоговом окне Разделить имена выполните следующие настройки.

3. В следующем всплывающем диалоговом окне Разделить имена выберите ячейку назначения для вывода разделённых текстов и нажмите OK. См. демонстрацию ниже.

Пример №8. Разделить текст и числа в ячейках

Предположим, у вас есть список текстовых строк, содержащих текст и числа, как показано на скриншоте ниже. Чтобы разделить текст и числа и поместить их в разные столбцы, мы предлагаем четыре способа.

Разделить текст и числа с помощью формул

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

Общие формулы

Получить текст из ячейки

=LEFT(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))-1)

Получить числа из ячейки

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

Затем примените эти две формулы в разных ячейках, чтобы получить отдельные текст и числа.

1. Выберите пустую ячейку для размещения текста первой текстовой строки из списка, скопируйте или введите формулу ниже и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить текст других строк из списка.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

2. Выберите ячейку (E5) рядом с первой ячейкой с результатом (D5), скопируйте или введите формулу ниже и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить числа других строк из списка.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Разделить текст и числа с помощью функции Быстрое заполнение (версии2013 и новее)

Если вы используете Excel2013 или более новую версию, вы можете воспользоваться встроенной функцией Быстрое заполнение для разделения текста и чисел из одной ячейки на два столбца.

Примечание: Чтобы функция Быстрое заполнение работала, столбцы с результатами должны находиться рядом с исходным столбцом с текстовой строкой. Например, если исходные строки находятся в столбце B, разделённые текст и числа должны быть в столбцах C и D. См. скриншот:

1. Вручную введите текст первой текстовой строки (D5) в ячейку C5.

2. Продолжайте вводить текст второй текстовой строки (D6) в ячейку C6.

3. Активируйте ячейку C7, нажмите Данные > Быстрое заполнение.

После этого тексты других строк будут автоматически заполнены в ячейках, как показано на скриншоте ниже.

4. Повторите шаги1–3, чтобы получить числа в столбце D.

Примечания:

Разделить текст и числа с помощью пользовательской функции

Если текстовые строки в списке нерегулярны, два вышеописанных способа могут вернуть неверные результаты, как показано на скриншоте ниже.

Здесь мы представляем пользовательскую функцию, которая поможет разделить текст и числа из ячейки на два столбца независимо от расположения чисел в строке. См. скриншот:

1. Нажмите Alt + F11.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем скопируйте приведённый ниже VBA в окно кода.

VBA-код: разделить текст и числа из ячейки на два столбца

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Нажмите Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Выберите ячейку для вывода текста первой текстовой строки, введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить текст других строк из того же списка.

=SplitText(B5,FALSE)

5. Выберите ячейку рядом с первой ячейкой с текстом для вывода чисел, введите приведённую ниже формулу и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить числа других строк.

=SplitText(B5,TRUE)

Разделить текст и числа с помощью удобного инструмента

Здесь мы рекомендуем самый простой способ разделить текст и числа из ячейки на два столбца. Использование функции Разделить ячейки в Kutools для Excel позволяет решить эту задачу всего за несколько кликов.

1. Выделите ячейки с текстовыми строками, которые хотите разделить на текст и числа в два столбца.

2. Нажмите Kutools > Объединить и разделить > Разделить ячейки.

3. В диалоговом окне Разделить ячейки выполните следующие настройки.

3.1) Выбранный диапазон отображается в Диапазон разделения поле, и вы можете нажать кнопку, чтобы выбрать новый диапазон при необходимости;

3.2) В разделе Тип выберите Разделить на строки или Разделить данные;

3.3) В разделе Разделить по выберите переключатель Разделить по тексту и числу;

3.4) Нажмите кнопку OK.

4. В следующем диалоговом окне Разделить ячейки выберите ячейку для вывода разделённых текста и чисел, затем нажмите кнопку OK.

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

2. Разделение числовых ячеек

В этой части описывается, как разделить числовые ячейки в двух случаях: разделение числа из нескольких цифр на отдельные цифры и разделение числа по десятичному знаку для выделения целой и дробной части.

Пример №1: Разделить ячейки по цифрам

Если вы хотите разделить число из нескольких цифр на отдельные цифры в разных столбцах, воспользуйтесь одним из следующих способов.

Разделить число в ячейке на отдельные цифры с помощью формулы

Приведённая ниже формула поможет разделить число на отдельные цифры и разместить их в разных столбцах.

Общая формула

=MID($A1, COLUMNS($A$1:A$1),1)

Аргумент

1. Выберите пустую ячейку для вывода первой цифры, введите приведённую ниже формулу и нажмите Enter.

=MID($B3, COLUMNS($B$3:B$3),1)

2. Выделите полученную ячейку и перетащите маркер автозаполнения вправо, чтобы получить остальные цифры. Оставьте все ячейки с результатами выделенными, затем перетащите маркер автозаполнения вниз, чтобы получить отдельные цифры других чисел.

Примечание: В этой формуле B3 — это ячейка с числом для разделения на отдельные цифры, вы можете изменить её по необходимости.

Разделить число в ячейке на отдельные цифры с помощью VBA

Приведённый ниже VBA-код также поможет разделить число в ячейке на отдельные цифры в Excel. Выполните следующие действия.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем скопируйте приведённый ниже VBA в окно кода.

VBA-код: разделить число на отдельные цифры в Excel

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

3. Нажмите клавишу F5 для запуска кода, затем появится диалоговое окно Kutools для Excel, в котором нужно выбрать диапазон числовых ячеек для разделения и нажать кнопку OK.

4. Появится второе диалоговое окно Kutools для Excel, в котором нужно выбрать ячейку для вывода отдельных цифр и нажать OK.

Примечание: Этот код также может разделять слово на отдельные буквы.

После этого числа в выбранных ячейках будут разделены на отдельные цифры и размещены в разных столбцах.

Легко разделить число на отдельные цифры с помощью удобного инструмента

Функция Разделить ячейки в Kutools для Excel — это удобный инструмент для быстрого разделения числа на отдельные цифры в Excel.

1. После установки Kutools для Excel выберите диапазон числовых ячеек для разделения, нажмите Kutools > Объединить и разделить > Разделить ячейки.

2. В диалоговом окне Разделить ячейки выполните следующие настройки.

2.1) В Диапазон разделения разделе вы увидите выбранный диапазон в текстовом поле. При необходимости вы можете нажать кнопку, чтобы изменить диапазон;

2.2) В разделе Тип выберите Разделить на строки или Разделить данные в зависимости от ваших потребностей;

2.3) В разделе Разделить по выберите переключатель Указать ширину, введите число1 в текстовое поле;

2.4) Нажмите кнопку OK.

3. В открывшемся диалоговом окне Разделить ячейки выберите пустую ячейку для вывода цифр и нажмите кнопку OK.

После этого числа в выбранных ячейках будут разделены на отдельные цифры одновременно.

Пример №2. Разделить число по десятичному знаку

В этом разделе рассматриваются несколько способов разделения числа на целую и дробную части в Excel.

Разделить число по десятичному знаку с помощью формул

Функцию TRUNC можно использовать для разделения числа по десятичному знаку в Excel. Выполните следующие действия.

Общие формулы

Получить целую часть

=TRUNC(A1)

Получить дробную часть

=A1-TRUNC(A1)

Аргумент

Теперь вы можете применить эти две формулы для разделения чисел в указанном диапазоне ячеек по десятичному знаку в Excel.

1. Выберите ячейку для размещения целой части первой числовой ячейки, введите формулу ниже и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить целые части других числовых ячеек.

=TRUNC(B5)

2. Выберите ячейку рядом с первой ячейкой с результатом для размещения дробной части, введите формулу ниже и нажмите Enter. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить дробные части других числовых ячеек.

=B5-TRUNC(B5)

Разделить число по десятичному знаку с помощью функции Разделить данные

Вы можете воспользоваться функцией Разделить данные для разделения числа по десятичному знаку в Excel.

1. Выделите диапазон числовых ячеек, которые хотите разделить по десятичному знаку, затем нажмите Данные > Разделить данные.

2. В диалоговом окне Мастер преобразования текста в столбцы — шаг1 из3 выберите переключатель С разделителями и нажмите кнопку Далее.

3. В диалоговом окне Шаг2 из3 отметьте только поле Другое, введите точку в текстовое поле и нажмите кнопку Далее.

4. В Шаг3 из3 диалоговом окне нажмите кнопку, чтобы выбрать ячейку назначения для вывода разделённых целых и дробных частей, затем нажмите Завершить кнопку. См. скриншот:

После этого вы увидите, что целая и дробная части разделены из выбранных ячеек, как показано на скриншоте ниже. Однако дробная часть теряет знак минус, который может быть важен для пользователя.

Разделить число по десятичному знаку с помощью функции Быстрое заполнение (версии2013 и новее)

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

1. Введите несколько примеров. В данном случае введите целую часть B5 в C5, целую часть B6 в C6. См. скриншот:

Примечание: Для отрицательных чисел не забудьте указать минус.

3. Выделите ячейки с примерами в столбце, который хотите заполнить, затем нажмите Данные > Быстрое заполнение.

После этого целые части будут извлечены из указанных числовых ячеек, как показано на скриншоте ниже.

4. Повторите вышеуказанные шаги для разделения дробных частей из тех же числовых ячеек.

3. Разделение ячеек с датой

Ещё одна часто встречающаяся ситуация — разделение ячеек с датой. Если вам нужно разделить ячейки с датой на отдельные день, месяц и год или разделить дату и время, используйте подходящее решение ниже.

Пример №1. Разделить ячейки с датой на отдельные день, месяц и год

Предположим, у вас есть список дат в диапазоне B5:B9, и вы хотите разделить каждую дату на три отдельных столбца: день, месяц и год. Вот три способа, которые помогут вам добиться этого результата.

Разделить ячейки с датой на день, месяц и год с помощью формул

Вы можете воспользоваться тремя формулами на основе функций ДЕНЬ, МЕСЯЦ и ГОД для разделения даты на отдельные день, месяц и год в Excel.

Общие формулы

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Аргумент

1. Создайте три столбца для размещения отдельных дня, месяца и года.

2. Выберите первую ячейку в столбце День, введите приведённую ниже формулу и нажмите Enter, чтобы получить день из первой ячейки с датой (B5). Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить дни из других ячеек с датой.

=DAY(B5)

3. Выполните аналогичные действия, чтобы применить следующие формулы в столбцах Месяц и Год для получения месяца и года из ячеек с датой.

Получить месяц из даты

=MONTH(B5)

Получить год из даты

=YEAR(B5)

Разделить ячейки с датой на день, месяц и год с помощью функции Разделить данные

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

Примечание: В диалоговом окне Шаг2 из3 отметьте только поле Другое и введите символ / в текстовое поле.

Разделить ячейки с датой на день, месяц и год с помощью удобного инструмента

В приведённой ниже демонстрации показано, как разделить дату на отдельные месяц, день и год с помощью функции Разделить ячейки в Kutools для Excel.

Эта функция позволяет массово разделять даты всего за несколько кликов.

Нажмите, чтобы узнать больше об этой функции.

Пример №2. Разделить дату и время в ячейке

Предположим, у вас есть список дат и времени в B5:B9, и вы хотите разделить даты и время на отдельные столбцы. В этом разделе показаны различные способы решения этой задачи.

Разделить дату и время в ячейке с помощью формулы

Вы можете воспользоваться следующими формулами для разделения даты и времени на разные столбцы в Excel.

1. Подготовьте два столбца для размещения дат и времени.

2. Необходимо установить для ячеек столбца с датой формат Дата, а для ячеек столбца с временем — формат Время.

3. Выберите первую ячейку в столбце Дата, введите приведённую ниже формулу и нажмите Enter, чтобы получить дату из B5. Выделите полученную ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные даты.

=INT(B5)

4. Примените следующую формулу в столбце времени, чтобы получить время из B5:B9.

=B5-D5

Разделить дату и время в ячейке с помощью функции Быстрое заполнение (версии2013 и новее)

Если вы используете Excel2013 или более новую версию, вы можете воспользоваться встроенной функцией Быстрое заполнение для разделения даты и времени на разные столбцы.

1. Создайте столбцы Дата и Время и введите несколько примеров, которые хотите получить на выходе. В столбце Дата введите дату из B5 в C5 и дату из B6 в C6. В столбце Время введите время из B5 в D5 и время из B6 в D6. См. скриншот:

2. Выделите столбец Дата (включая несколько примеров), затем нажмите Данные > Быстрое заполнение.

3. Выделите столбец Время (включая несколько примеров), затем также включите функцию Быстрое заполнение. После этого даты и время из B5:B9 будут разделены на отдельные столбцы, как показано в демонстрации ниже.

Теперь вы узнали, как разделять ячейки в Excel разными способами в различных ситуациях. Вы сможете выбрать наиболее подходящий метод в зависимости от вашей задачи.