Иногда, работая в Excel, вы будете сталкиваться с данными, в которых текст и числа объединены намеренно или непреднамеренно. Например, определенное число, состоящее из нескольких цифр, и предшествующий ему текст. Задача — отделить это число от текста. Или это некий набор буквенно-цифровых элементов, в котором необходимо отделить буквы от цифр.
При этом в Excel по умолчанию нет функций или процедур, которые исполняют наши планы. Возможно? Ответ — да, с последующим пошаговым руководством.
Перед тем, как приступить к непосредственному решению проблемы, желательно сначала найти числа в каждой ячейке и аналогичным образом проверить, содержат ли одни и те же ячейки буквы, обычно кириллические или латинские. Это уменьшит ресурсы обработки, необходимые, если в данных много строк.
В этой статье я рассмотрю существующие способы разделения текста и чисел, как с использованием стандартных функций Excel, так и с использованием сторонних решений, включая мою надстройку Excel.
Примечание. Сюда входит разделение текста и чисел в ячейке. Если вы хотите выделить числа в отдельной ячейке, прочтите эту статью: Извлечение чисел из ячейки в Excel.
Отделить цифры от текста — формулы Excel
Начнем со стандартных функций Excel. С их помощью будет проще решать задачи, когда количество текстовых и цифровых пробелов в строке определено и не меняется от строки к строке. Наиболее популярные варианты:
- буквы перед цифрами (x100, FX5200, MI6)
- цифра / цифры перед буквой / буквами (2021, 3D, 4K, 4,2Ghz)
- номер-текст-номер (3k2, 22str3, 1EKX23)
Отделить число справа от текста
Если мы знаем, что ячейка начинается с букв и заканчивается цифрами, нам нужно найти позицию первой цифры в ячейке. Перейдите по ссылке, чтобы прочитать подробности, но вот сама формула:
{= SEARCH (FALSE; ISH (MID (A1; STRING (INDIRECT («1:» & DLSTR (A1)))); 1) -1); 0)} Это формула массива! Как и все подобные (с фигурными скобками), он также вставляется: — без фигурных скобок, — комбинацией CTRL + Shift + Enter (а не просто Enter).
К тому же это уже вопрос техники: получить фрагменты в этой позиции и начиная с нее помогут в этом функции LEFT и PSTR. Предположим, что позиция вычисляется в ячейке A2, а исходный текст — в ячейке A1, формулы для первой и второй части будут соответственно:
= ЛЕВЫЙ (A1; A2-1) = СРЕДНИЙ (A1; A2; 1000) 
Если вы хотите отобразить весь результат через разделитель в ячейке одновременно, вам нужно составить формулу из трех предыдущих:
{= LEFT (A1; ПОИСК (FALSE; EOSH (MID (A1; LINE (INDIRECT («1:» & DLSTR (A1)))); 1) -1); 0) -1) & «» & MID (A1; SEARCH (FALSE; EOSH (MID (A1; STRING (INDIRECT («1:» & DLSTR (A1)))); 1) -1); 0); 9999)}
В этой формуле в качестве разделителя используется пробел (в средней строке). Если вы хотите использовать другой разделитель, замените его своим.
Отделить цифры, стоящие перед текстом
Если вы заранее знаете, что текст ячейки начинается с цифр и продолжается текстовыми символами, вам необходимо получить позицию первого нечислового символа. Это можно сделать с помощью аналогичной формулы массива, с той лишь разницей, что функция ПОИСКПОЗ ищет ИСТИНА, а не ЛОЖЬ:
{= ПОИСК (ИСТИНА, ISH (MID (A1; СТРОКА (КОСВЕННАЯ («1:» & DLSTR (A1)))); 1) -1); 0)}
Также процедура аналогична первому примеру.
Регулярные выражения
Когда порядок текста и чисел в ячейке и количество фрагментов заранее неизвестны, регулярные выражения очень помогают.
К сожалению, в настоящее время они не предоставляются в Excel по умолчанию, но доступны по умолчанию в таблицах Google.
В Гугл таблицах
Формула электронной таблицы Google, разделяющая текст и числа, довольно проста:
= ОБРЕЗАТЬ (REGEXREPLACE (A1; «(\ d + \ D +)»; «$ 1 «)) 
Вы можете узнать больше о регулярных выражениях и о том, как они работают, в моей статье на этом сайте: Регулярные выражения в Excel (ссылка).
В Excel с надстройкой !SEMTools
Однако для всех пользователей моей надстройки функции регулярных выражений доступны бесплатно без каких-либо ограничений — просто подключите надстройку к Excel. Важно понимать, что функции будут корректно работать только на компьютерах, на которых они установлены.
Разделите текст и числа, разделенные пробелами, используя регулярное выражение в Excel с
Разбить текст и цифры с !SEMTools в 2 клика
Для тех, кто ценит время, я разработал процедуру, которая разделяет текст и числа внутри ячейки или отображает результат этой процедуры в соседнем столбце — это зависит от глобальной настройки вывода результатов.
Все, что вам нужно сделать, это:
- просто выделите диапазон или ячейку,
- вызвать процедуру,
- указать разделитель
- и нажмите ОК.
Разложение текста и чисел с помощью процедуры надстройки.
