Разделить текст и цифры в ячейке Excel, когда буквы идут перед числом (FX5200, x100), можно двумя формулами: =ЛЕВСИМВ(A2;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-1) вернёт текст, а =ПСТР(A2;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"));1000) — число. МИН(НАЙТИ(…)) находит позицию первой цифры. Если порядок текста и цифр заранее неизвестен, поможет регулярное выражение =СЖПРОБЕЛЫ(REGEXREPLACE(A2;"(\d+|\D+)";"$1 ")), а разбить текст и цифры прямо в ячейках, без формул, можно в 2 клика процедурой !SEMTools.
Прежде, чем приступать к непосредственному решению задачи, рекомендуется сначала найти числа в каждой ячейке и аналогично проверить, что эти же ячейки содержат буквы, обычно кириллицы или латиницы. Это позволит сократить требуемые вычислительные ресурсы, если строк в данных много.
Примечание: речь в ней о разделении текста и цифр внутри ячейки. Если вы хотите выделить цифры в отдельную ячейку - читайте эту статью: извлечь цифры из ячейки в Excel.
Отделить цифры от текста формулами Excel
Начнём со штатных возможностей Excel. Наиболее несложно будет решить с их помощью задачи, когда количество текстовых и цифровых промежутков в строке определено и не меняется от строки к строке. Наиболее популярные варианты:
- буквы перед цифрами (x100, FX5200, MI6);
- цифра/цифры перед буквой/буквами (2021г, 3D, 4K, 4,2Ghz);
- число-текст-число (3к2, 22стр3, 1ЕКХ23).
Отделить число справа от текста
Если ячейка начинается с букв, а заканчивается цифрами, нужно найти позицию первой цифры. Проще всего — функцией НАЙТИ, которая ищет сразу все 10 цифр, и МИН, которая выбирает самую левую. Дописанные «0123456789» страхуют от ошибки, если какой-то цифры в строке нет. Формула работает в любой версии Excel без Ctrl+Shift+Enter:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Артикул | Позиция цифры | Текст | Число |
| 2 | FX5200 | 3=МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A2&"0123456789")) | FX=ЛЕВСИМВ(A2;B2-1) | 5200=ПСТР(A2;B2;1000) |
| 3 | x100 | 2=МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A3&"0123456789")) | x=ЛЕВСИМВ(A3;B3-1) | 100=ПСТР(A3;B3;1000) |
| 4 | MI6 | 3=МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A4&"0123456789")) | MI=ЛЕВСИМВ(A4;B4-1) | 6=ПСТР(A4;B4;1000) |
| 5 | SKU-0451 | 5=МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A5&"0123456789")) | SKU-=ЛЕВСИМВ(A5;B5-1) | 0451=ПСТР(A5;B5;1000) |
В B2 позиция первой цифры — 3. ЛЕВСИМВ берёт символы до неё, ПСТР — всё, начиная с неё. В SKU-0451 ведущий ноль сохранился, потому что результат — текст.
В английском Excel: =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Тот же результат даёт формула массива из статьи про позицию первой цифры — её вводят сочетанием Ctrl+Shift+Enter (в Excel 365 — просто Enter):
=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)
Если хочется вывести весь результат через разделитель сразу в одну ячейку, нужно составить формулу из трёх вышеуказанных (вводится как формула массива):
{=ЛЕВСИМВ(A1;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)-1)
&" "&
ПСТР(A1;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0);9999)}
Данная формула имеет разделителем пробел (в строке посередине), если хотите использовать другой разделитель, просто замените его на свой.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Позиция цифры | Через пробел |
| 2 | FX5200 | 3=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)-1);0) | FX 5200=ЛЕВСИМВ(A2;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)-1);0)-1)&" "&ПСТР(A2;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)-1);0);9999) |
| 3 | x100 | 2=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A3)));1)-1);0) | x 100=ЛЕВСИМВ(A3;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A3)));1)-1);0)-1)&" "&ПСТР(A3;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A3)));1)-1);0);9999) |
| 4 | MI6 | 3=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A4;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A4)));1)-1);0) | MI 6=ЛЕВСИМВ(A4;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A4;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A4)));1)-1);0)-1)&" "&ПСТР(A4;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A4;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A4)));1)-1);0);9999) |
ПСТР(…)-1 даёт ошибку для букв и число для цифр, ЕОШ превращает это в ИСТИНА/ЛОЖЬ, а ПОИСКПОЗ находит первую ЛОЖЬ — первую цифру. В Excel 2019 и раньше — Ctrl+Shift+Enter.
Отделить цифры, стоящие перед текстом
Если ячейка начинается с цифр, ищем позицию первого символа, который не является цифрой: ПОИСКПОЗ ищет ИСТИНА в массиве «символ — не цифра». В Excel 365 формула вводится обычным Enter, в более ранних версиях — Ctrl+Shift+Enter:
| A | B | C | |
|---|---|---|---|
| 1 | Значение | Число | Текст |
| 2 | 2021г | 2021=ЛЕВСИМВ(A2;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A2;СТРОКА($1:$20);1));0)-1) | г=ПСТР(A2;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A2;СТРОКА($1:$20);1));0);1000) |
| 3 | 4K | 4=ЛЕВСИМВ(A3;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A3;СТРОКА($1:$20);1));0)-1) | K=ПСТР(A3;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A3;СТРОКА($1:$20);1));0);1000) |
| 4 | 15кг | 15=ЛЕВСИМВ(A4;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A4;СТРОКА($1:$20);1));0)-1) | кг=ПСТР(A4;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A4;СТРОКА($1:$20);1));0);1000) |
| 5 | 128GB | 128=ЛЕВСИМВ(A5;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A5;СТРОКА($1:$20);1));0)-1) | GB=ПСТР(A5;ПОИСКПОЗ(ИСТИНА;ЕОШ(--ПСТР(A5;СТРОКА($1:$20);1));0);1000) |
СТРОКА($1:$20) перебирает первые 20 символов — этого достаточно для коротких значений. ПСТР за пределами строки возвращает пустоту, а её тоже нельзя превратить в число, поэтому формула не ломается.
В английском Excel: =LEFT(A2,MATCH(TRUE,ISERROR(--MID(A2,ROW($1:$20),1)),0)-1)
Разделить текст и цифры регулярными выражениями
Когда порядок расположения текста и цифр в ячейке и количество фрагментов заранее не известно, отличным подспорьем станут регулярные выражения.
В Excel 365 есть встроенная функция РЕГЗАМЕНИТЬ (REGEXREPLACE): =СЖПРОБЕЛЫ(РЕГЗАМЕНИТЬ(A1;"(\d+|\D+)";"$1 ")) вставит пробел между всеми группами букв и цифр. Шаблон «\d+» — группа цифр, «\D+» — группа не-цифр, «$1 » — найденная группа и пробел после неё. Та же формула работает в Google Таблицах и в Excel с !SEMTools.
| A | B | |
|---|---|---|
| 1 | Текст | Части через пробел |
| 2 | FX5200 | FX 5200=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"(\d+|\D+)";"$1 ")) |
| 3 | 2021г | 2021 г=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"(\d+|\D+)";"$1 ")) |
| 4 | iPhone15Pro | iPhone 15 Pro=СЖПРОБЕЛЫ(REGEXREPLACE(A4;"(\d+|\D+)";"$1 ")) |
Шаблон находит подряд идущие цифры или подряд идущие нецифры и ставит после каждого фрагмента пробел. СЖПРОБЕЛЫ убирает пробел в конце.
В английском Excel: =TRIM(REGEXREPLACE(A2,"(\d+|\D+)","$1 "))
В Google Таблицах
В Google Таблицах с английской локалью аргументы разделяются запятыми:
=TRIM(REGEXREPLACE(A1,"(\d+|\D+)","$1 "))
Подробнее о регулярных выражениях и том, как они работают, можно почитать в моей статье на этом сайте: Регулярные выражения в Excel.
В Excel с надстройкой !SEMTools
В версиях Excel до 365 функции регулярных выражений появляются вместе с надстройкой — бесплатно и без ограничений. Важно понимать, что функции будут работать корректно только на компьютерах, где она установлена.
Разделить текст и цифры в 2 клика с !SEMTools
Для тех, кто ценит время, я разработал процедуру, которая разбивает текст и цифры прямо внутри ячейки, или выводит результат такой процедуры в соседний столбец - это зависит от глобальной настройки вывода результатов.
Все, что вам нужно сделать, это:
- просто выделить диапазон или ячейку
- вызвать процедуру
- указать разделитель
- нажать "ОК"
Почему текст и цифры разделяются неправильно
- Дробное число разбилось на части. Запятая — не цифра, поэтому «4,2Ghz» разделится на «4» и «,2Ghz». Для таких данных используйте регулярное выражение с дробной частью: =REGEXEXTRACT(A2;"\d+([.,]\d+)?").
- Ошибка #Н/Д. В ячейке нет ни одной цифры (или ни одной буквы для формулы с ИСТИНА) — ПОИСКПОЗ ничего не находит. Оберните формулу в ЕСЛИОШИБКА.
- Число не суммируется. ЛЕВСИМВ и ПСТР возвращают текст. Если нужно число, добавьте два минуса: =--ПСТР(A2;B2;1000). Для артикулов оставляйте текст, чтобы не потерять ведущие нули.
- Ошибка #ИМЯ? в формуле с регулярным выражением. В вашей версии Excel нет РЕГЗАМЕНИТЬ — используйте формулы выше или функцию REGEXREPLACE из !SEMTools.
Разделить текст и цифры — частые вопросы
Как отделить цифры от текста в ячейке?
Найдите позицию первой цифры формулой =МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A2&"0123456789")), затем возьмите текст ЛЕВСИМВ до неё и число ПСТР начиная с неё.
Как разделить текст и цифры пробелом в одной ячейке?
Регулярным выражением: =СЖПРОБЕЛЫ(REGEXREPLACE(A2;"(\d+|\D+)";"$1 ")). В Excel 365 функция называется РЕГЗАМЕНИТЬ.
Как разнести текст и цифры по разным столбцам?
Формулами ЛЕВСИМВ и ПСТР из этой статьи в два соседних столбца или процедурой !SEMTools с выводом результата в соседний столбец.
Можно ли разделить текст и цифры без формул?
Да, мгновенным заполнением: введите в соседнем столбце текстовую часть первой ячейки и нажмите Ctrl+E. Для разнородных данных надёжнее процедура !SEMTools.
Разделить текст и цифры прямо в ячейках или с выводом в соседний столбец можно в 2 клика с !SEMTools — без формул массива и поиска позиции первой цифры.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как разделить текст и цифры в Excel
Надеюсь, на этой странице вы нашли то, что искали, и смогли отделить цифры от текста. Если структура у всех ячеек одинаковая — «буквы, потом цифры» или наоборот, — хватит формул с позицией первой цифры. Если порядок разный, удобнее регулярное выражение, а для регулярной работы — процедура прямо на месте. Вас наверняка заинтересуют и похожие операции: извлечь цифры из ячейки и удалить цифры из текста.