Разделение Разделить по тексту и числу в ячейке Excel

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

Как разделить данные в ячейке Excel на текст и число?

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

Во-первых, необходимо определить позицию первой цифры;
во-вторых — извлечь текст из ячейки;
в-третьих — получить цифры.

Универсальные формулы

Универсальная формула для определения позиции первой цифры

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

Универсальная формула для получения текста

=LEFT(A1,position-1)

Универсальная формула для получения цифр

=RIGHT(A1,LEN(A1)-position+1)

Аргументы приведённых выше формул

Как использовать эти формулы?

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

Во-первых, определите начальную позицию цифр в текстовой строке

1. Выберите пустую ячейку для отображения позиции — в данном примере это ячейка C3.

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

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

Во-вторых, получите текст

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

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

=LEFT(B3,C3-1)

В-третьих, получите цифры

1. Выберите пустую ячейку, например E3, для размещения цифр.

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

=RIGHT(B3,LEN(B3)-C3+1)

Как работают эти формулы?

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

1. FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789")

• B3&"0123456789"возвращает результат: Ana Varela 240,150123456789.
• FIND({0,1,2,3,4,5,6,7,8,9}, "ana varela 240.150123456789"): функция НАЙТИ определяет позиции цифр «0–9» в строке «ana varela 240,150123456789» и возвращает результат {14,16,12,21,13,17,24,25,26,27}.
  Результат {14,16,12,21,13,17,24,25,26,27} означает, что цифра 0 находится на 14-й позиции в строке «ana varela 240,150123456789», цифра 1 — на 16-й позиции в строке «ana varela 240,150123456789»…

2. =MIN({14,16,12,21,13,17,24,25,26,27}): функция МИН возвращает минимальное значение из массива. В данном случае результат равен 12.

=LEFT(B3,C3-1)

LEFT(ana varela 240.15,12-1)Функция ЛЕВСИМВ извлекает первые 11 символов с начала текстовой строки «ana varela 240,15», и окончательный результат — «ana varela».

=RIGHT(B3,LEN(B3)-C3+1)

1. LEN(B3)-C3+1:

• Функция ДЛСТР вычисляет общую длину текстовой строки «ana varela 240,150123456789» в ячейке B3 и возвращает результат — 17.
• 17-12+1: здесь вычисляется общая длина цифр в строке «ana varela 240,15». Результат равен 6.

2. =RIGHT(B3,6)Функция ПРАВСИМВ извлекает 6 символов с правого края строки «ana varela 240,15», и окончательный результат — «240,15».

Связанные функции

Связанные формулы