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

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

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

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

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

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

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

Встроенная в Excel функция «Текст по столбцам» часто используется для разделения содержимого ячеек. Как показано на снимке экрана ниже, чтобы разделить ячейки в столбце «Текстовые строки» по запятой, выполните следующие действия с помощью функции «Текст по столбцам».

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’s Разделить ячейки, которая поможет вам легко разделить ячейки на отдельные столбцы или строки по указанному разделителю в Excel.

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

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

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

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

Разделить на столбцы:

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

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

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

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

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

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

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(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 для приложений.

2. В окне Microsoft Visual Basic для приложений выберите пункт меню Вставка > Модуль. Затем скопируйте приведённый ниже код 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(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 для приложений.

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)

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

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

ПримечаниеЧтобы функция «Быстрое заполнение» работала корректно, столбцы с результатами должны располагаться непосредственно после столбца с исходными данными. Например, если исходный текст находится в столбце 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) Нажмите кнопку ОК.

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 для приложений.

2. В окне Microsoft Visual Basic для приложений выберите Вставка > Модуль, а затем скопируйте приведённый ниже код 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 — выберите диапазон ячеек с числами для разделения и нажмите кнопку ОК.

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

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

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

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

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

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

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

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

2,2) В разделе Типвыберите нужный вариант Разделить на строкиили Разделить на столбцыв соответствии с вашими требованиями;

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

2,4) Нажмите кнопку ОК.

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

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

Пример #2 Разделение числа по десятичной точке

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

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

Функцию ОТБР можно использовать в 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. Выполните ту же операцию, что и на шаге 1, чтобы применить следующие формулы в столбцах Месяц и Год и получить отдельные значения месяца и года из ячеек с датами.

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

=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

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

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

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

После этого выделите столбец, который требуется заполнить, и выберите Данные>Быстрое заполнение.

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

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

Лучшие инструменты повышения продуктивности в Office

🤖	KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
	Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
	Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
	Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
	Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
	Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
	Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое

Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

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

Подробнее о Kutools для Excel…Бесплатная загрузка…

---

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

• Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
• Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее о Office Tab…Бесплатная загрузка…

---

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

• Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
• Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
• Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
• 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
• Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек

Подробнее о Kutools for Office…Бесплатная загрузка…