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

1. После установки Kutools for Excel, выберите диапазон, в котором вы хотите разделить текстовые строки, а затем щелкните Кутулс > Слияние и разделение > Разделить клетки.

2. в Разделить клетки диалоговое окно необходимо настроить следующим образом.

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

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

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

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

Пример # 2 Разделение ячеек на определенную длину

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

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

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

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

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

2. Нажмите Данные > Текст в столбцы.

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

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

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

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

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

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

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

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

2. в Разделить клетки диалоговое окно необходимо настроить следующим образом.

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

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

Пример # 3 Разделение ячеек по определенному слову

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

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

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

= ЛЕВЫЙ (B5; ПОИСК (СИМВОЛ (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; ПОИСК (СИМВОЛ (10); B5) + 1, ПОИСК (СИМВОЛ (10), B5, ПОИСК (СИМВОЛ (10), B5) +1) - ПОИСК (СИМВОЛ (10), B5) - 1 )

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

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

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

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

аргументы

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

= ВПРАВО (B5; LEN (B5) - ПОИСК (СИМВОЛ (10), B5, ПОИСК (СИМВОЛ (10), B5) + 1))

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

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

1. нажмите другой + 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 for Excel появится диалоговое окно, вам нужно выбрать диапазон ячеек, которые вы хотите разделить по разрыву строки, и, наконец, щелкнуть OK кнопку.

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

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

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

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

2. Нажмите Кутулс > Слияние и разделение > Разделить клетки для включения функции.

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

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

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

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Пример # 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. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

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

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

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

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

Пример # 7 Разделение имен в ячейках

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

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

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

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

Вам нужно применить две формулы отдельно, чтобы разделить полное имя на имя и фамилию.

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

Вы можете применить формулу, основанную на функциях ВЛЕВО и ПОИСК, чтобы отделить имя от полного имени.

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

=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 for Excel. С помощью этой функции вы можете легко разделить полное имя на имя и фамилию всего несколькими щелчками мыши.

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

2. в Разделить имена диалоговое окно, только отметьте Имя и Фамилия коробка в Сплит-типы раздел, а затем щелкните OK.

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

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

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

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

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

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

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

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

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

Внимание: В мастере Шага 2 из 3 проверьте только Space пунктом.

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 for ExcelАвтора Разделить имена функция, которая позволяет разделить полное имя на имя, отчество и фамилию сразу с помощью всего нескольких щелчков мышью.

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

2. в Разделить имена диалоговое окно необходимо настроить следующим образом.

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

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Пример # 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)

Разделение текста и чисел с помощью Flash Fill (2013 г. и более поздние версии)

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

Внимание: Чтобы заставить работать функцию Flash Fill, вам нужно расположить столбцы результатов рядом с столбцом исходной текстовой строки. Например, если исходные текстовые строки находятся в столбце B, разделенный текст и числа должны быть помещены в столбцы C и D. См. Снимок экрана:

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

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

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

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

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

Заметки:

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

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

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

1. нажмите другой + F11 ключи.

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

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

=SplitText(B5,FALSE)

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

=SplitText(B5,TRUE)

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

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

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

2. Нажмите Кутулс > Слияние и разделение > Разделить клетки.

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

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

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

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

---

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. нажмите другой + 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 for Excel появится диалоговое окно, вам нужно выбрать диапазон числовых ячеек, которые вы разделите, а затем щелкнуть OK кнопку.

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

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

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

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

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

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

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

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

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

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Пример # 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 диалогового окна, нажмите кнопку, чтобы выбрать ячейку назначения для вывода разделенных целых и десятичных чисел, и, наконец, нажмите кнопку Завершить кнопка. Смотрите скриншот:

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

Разделить число на десятичное с помощью Flash Fill (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 for Excel.

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

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

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Пример # 2 Разделение даты и времени в ячейке

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

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

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

1. Подготовьте две колонки для размещения даты и времени.

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

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

=INT(B5)

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

=B5-D5

Разделение даты и времени в ячейке с помощью Flash Fill (2013 г. и более поздние версии)

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

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

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

3. Выберите столбец «Время» (включая несколько примеров), который вы хотите заполнить, а затем включите Вспышка заливки особенность. Затем даты и время в B5: B9 разбиваются на отдельные столбцы, как показано в демонстрации ниже.

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

---