Как разделить текст и цифры в Excel
Порой при работе в Excel встречаются данные, в которых умышленно или неумышленно сцеплены текст и цифры. Например, определенное число из нескольких цифр, и текст перед ним. Стоит задача отделить это число от текста. Или это некий набор буквенно-числовых артикулов, в которых нужно отделить буквы от цифр.
При этом ни функций, ни процедур, которые выполняли бы задуманное, в Excel по умолчанию нет. Можно ли? Ответ — да, а далее — пошаговое руководство.
Прежде, чем приступать к непосредственному решению задачи, рекомендуется сначала найти числа в каждой ячейке и аналогично проверить, что эти же ячейки содержат буквы, обычно кириллицы или латиницы. Это позволит сократить требуемые вычислительные ресурсы, если строк в данных много.
В этой статье я рассматриваю существующие способы разделения текста и цифр, как с помощью стандартных функций Excel, так и с помощью сторонних решений, в числе которых моя надстройка для Excel.
Примечание: речь в ней о разделении текста и цифр внутри ячейки. Если вы хотите выделить цифры в отдельную ячейку — читайте эту статью: извлечь цифры из ячейки в Excel.
Отделить цифры от текста — формулы Excel
Начнем с штатных возможностей Excel. Наиболее несложно будет решить с их помощью задачи, когда количество текстовых и цифровых промежутков в строке определено и не меняется от строки к строке. Наиболее популярные варианты:
- буквы перед цифрами (x100, FX5200, MI6)
- цифра/цифры перед буквой/буквами (2021г, 3D, 4K, 4,2Ghz)
- число-текст-число (3к2, 22стр3, 1ЕКХ23)
Отделить число справа от текста
Если мы знаем, что ячейка начинается с букв, а заканчивается цифрами, нужно найти позицию первой цифры в ячейке. По ссылке читайте подробности, а вот сама формула:
Далее уже дело техники — получить фрагменты до этой позиции и начиная с нее, в этом помогут функции ЛЕВСИМВ и ПСТР. Допустим, если позиция будет вычислена в ячейке A2, а исходный текст в ячейке A1, то формулы для первой и второй части будут, соответственно:
Если хочется вывести весь результат через разделитель сразу в одну ячейку, нужно составить формулу из трех вышеуказанных:
Данная формула имеет разделителем пробел (в строке посередине), если хотите использовать другой разделитель, просто замените его на свой.
Отделить цифры, стоящие перед текстом
Если же заранее известно, что текст ячейки начинается с цифр и продолжается текстовыми символами, нужно получить позицию первого нечислового символа. Это можно сделать с помощью аналогичной формулы массива, единственная разница лишь в том, что функция ПОИСКПОЗ ищет ИСТИНА, а не ЛОЖЬ:
Далее порядок действий аналогичен первому примеру.
Регулярные выражения
Когда порядок расположения текста и цифр в ячейке и количество фрагментов заранее не известно, отличным подспорьем станут регулярные выражения.
К сожалению, на текущий момент они не поставляются в Excel из коробки, но доступны по умолчанию в гугл таблицах.
В Гугл таблицах
Формула Гугл таблиц, которая разделит текст и цифры, довольно незамысловата:
Подробнее о регулярных выражениях и том, как они работают, можно почитать в моей статье на этом сайте: Регулярные выражения в Excel (ссылка).
В Excel с надстройкой !SEMTools
Тем не менее, всем пользователям моей надстройки функции регулярных выражений доступны бесплатно безо всяких ограничений — нужно просто подключить надстройку к Excel. Важно понимать, что функции будут работать корректно только на компьютерах, где она установлена.
Разделить текст и числа через пробел с помощью регулярных выражений в Excel с !SEMTools
Разбить текст и цифры с !SEMTools в 2 клика
Для тех, кто ценит время, я разработал процедуру, которая разбивает текст и цифры прямо внутри ячейки, или выводит результат такой процедуры в соседний столбец — это зависит от глобальной настройки вывода результатов.
Все, что вам нужно сделать, это:
- просто выделить диапазон или ячейку,
- вызвать процедуру,
- указать разделитель
- и нажать ОК.
Разбивка текста и цифр процедурой надстройки
Хотите так же быстро выполнять данную процедуру? Попробуйте тестовую версию надстройки !SEMTools:
Как убрать пробелы в цифрах в Excel: пошаговая инструкция
В некоторых случаях требуется убрать пробелы в цифрах в таблице Excel, для того, чтобы производить вычисления, а выполнить это можно при помощи инструкции ниже.
- Способ №1;
- Способ №2;
- Способ №3;
- Способ №4.
Как убрать пробелы в цифрах в Excel (Способ №1)
Для этого необходимо использовать формулу:
Способ №2
Выполнить можно выделив нужный диапазон для изменения, после чего нажимаем на «Главной»-«Найти и заменить».
После в первом поле вводим пробел, а второе оставляем пустым.
Как удалить пробелы в цифрах в Эксель (Способ №3)
Дополнительно для решения проблемы можно воспользоваться конструкция:
Способ №4
Выделив нужный диапазон нажимаем правой клавишей мыши после чего выбираем «Формат ячеек».
После этого указываем числовой с 0 знаков после запятой.
Подписывайтесь на наш канал в Яндекс.Дзен
Подписывайтесь на наши социальные сети
Вам также будет интересно
Добавить комментарий Отменить ответ
Наши социальные сети
- CRM-системы (8)
- Excel (73)
- Google Ads (48)
- Google Data Studio (25)
- Google Optimize (2)
- Google Search Console (18)
- Google Аналитика (45)
- Google Менеджер Тегов (4)
- Google Мой бизнес (3)
- Opencart (31)
- SEO (77)
- SMM (24)
- WordPress (19)
- Yandex DataLens (3)
- Веб-аналитика (140)
- Визуализация данных (13)
- Домены (15)
- Кейсы (6)
- Контекстная реклама (100)
- Локальное SEO (7)
- Полезное (64)
- Сайтостроение (84)
- Таргетинг (31)
- Формулы (16)
- Яндекс.Вебмастер (49)
- Яндекс.Директ (60)
- Яндекс.Маркет (15)
- Яндекс.Метрика (39)
- Яндекс.Справочник (6)
© 2021
Сео Пульс — блог об интернет-маркетинге