Разделение текста и чисел в одной ячейке в 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 varela240.150123456789.
• FIND({0,1,2,3,4,5,6,7,8,9}, "ana varela240.150123456789")Функция Find определяет позиции цифр "0-9" в строке ana varela240.150123456789 и возвращает результат {14,16,12,21,13,17,24,25,26,27}.
  Результат {14,16,12,21,13,17,24,25,26,27} означает, что цифра0 находится на14-й позиции в строке ana varela240.150123456789, цифра1 — на16-й позиции и так далее.

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

=LEFT(B3,C3-1)

LEFT(ana varela240.15,12-1) означает, что функция LEFT извлекает первые11 символов слева из строки "ana varela240.15". Итоговый результат — ana varela.

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

1. LEN(B3)-C3+1:

• Функция LEN вычисляет общую длину текстовой строки (ana varela240.150123456789) в ячейке B3 и возвращает результат17.
• 17-12+1: Здесь определяется общая длина числовой части в ana varela240.15. Результат —6.

2. =RIGHT(B3,6): Функция RIGHT извлекает6 символов справа из строки ana varela240.15. Итоговый результат —240.15.

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

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