Как разделить текст и цифры в Excel
Порой при работе в Excel встречаются данные, в которых умышленно или неумышленно сцеплены текст и цифры. Например, определенное число из нескольких цифр, и текст перед ним. Стоит задача отделить это число от текста. Или это некий набор буквенно-числовых артикулов, в которых нужно отделить буквы от цифр.
При этом ни функций, ни процедур, которые выполняли бы задуманное, в Excel по умолчанию нет. Можно ли? Ответ — да, а далее — пошаговое руководство.
Прежде, чем приступать к непосредственному решению задачи, рекомендуется сначала найти числа в каждой ячейке и аналогично проверить, что эти же ячейки содержат буквы, обычно кириллицы или латиницы. Это позволит сократить требуемые вычислительные ресурсы, если строк в данных много.
В этой статье я рассматриваю существующие способы разделения текста и цифр, как с помощью стандартных функций Excel, так и с помощью сторонних решений, в числе которых моя надстройка для Excel.
Примечание: речь в ней о разделении текста и цифр внутри ячейки. Если вы хотите выделить цифры в отдельную ячейку — читайте эту статью: извлечь цифры из ячейки в Excel.
Отделить цифры от текста — формулы Excel
Начнем с штатных возможностей Excel. Наиболее несложно будет решить с их помощью задачи, когда количество текстовых и цифровых промежутков в строке определено и не меняется от строки к строке. Наиболее популярные варианты:
Отделить число справа от текста
Если мы знаем, что ячейка начинается с букв, а заканчивается цифрами, нужно найти позицию первой цифры в ячейке. По ссылке читайте подробности, а вот сама формула:
Далее уже дело техники — получить фрагменты до этой позиции и начиная с нее, в этом помогут функции ЛЕВСИМВ и ПСТР. Допустим, если позиция будет вычислена в ячейке A2, а исходный текст в ячейке A1, то формулы для первой и второй части будут, соответственно:
Если хочется вывести весь результат через разделитель сразу в одну ячейку, нужно составить формулу из трех вышеуказанных:
Данная формула имеет разделителем пробел (в строке посередине), если хотите использовать другой разделитель, просто замените его на свой.
Отделить цифры, стоящие перед текстом
Если же заранее известно, что текст ячейки начинается с цифр и продолжается текстовыми символами, нужно получить позицию первого нечислового символа. Это можно сделать с помощью аналогичной формулы массива, единственная разница лишь в том, что функция ПОИСКПОЗ ищет ИСТИНА, а не ЛОЖЬ:
Регулярные выражения
Когда порядок расположения текста и цифр в ячейке и количество фрагментов заранее не известно, отличным подспорьем станут регулярные выражения.
К сожалению, на текущий момент они не поставляются в Excel из коробки, но доступны по умолчанию в гугл таблицах.
В Гугл таблицах
Формула Гугл таблиц, которая разделит текст и цифры, довольно незамысловата:
Подробнее о регулярных выражениях и том, как они работают, можно почитать в моей статье на этом сайте: Регулярные выражения в Excel (ссылка).
В Excel с надстройкой !SEMTools
Тем не менее, всем пользователям моей надстройки функции регулярных выражений доступны бесплатно безо всяких ограничений — нужно просто подключить надстройку к Excel. Важно понимать, что функции будут работать корректно только на компьютерах, где она установлена.
Разделить текст и числа через пробел с помощью регулярных выражений в Excel с !SEMTools
Разбить текст и цифры с !SEMTools в 2 клика
Для тех, кто ценит время, я разработал процедуру, которая разбивает текст и цифры прямо внутри ячейки, или выводит результат такой процедуры в соседний столбец — это зависит от глобальной настройки вывода результатов.
Отделить цифры от текста в Excel, а также решить сотни других рутинных задач поможет надстройка !SEMTools
Скачайте надстройку прямо сейчас и начинайте экономить рабочее время!
Как ограничить строки и столбцы на листе Excel
Далее, как и в первом варианте, просто тянете маркер выделения вниз. В зависимости от того, с какой ячейки вы начинаете, вместо «А1» указываете нужную. В целом, этот подход не даёт никаких преимуществ в сравнении с первым, поэтому делайте как вам удобнее.
[expert_bq id=»1570″]Совет Для более продвинутой сортировки попробуйте в раскрывающимся меню Сортировка изменить тип сортировки на расширенные функции, такие как сортировка на основе цвета ячейки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Совет: Для более продвинутой сортировки попробуйте в раскрывающимся меню Сортировка изменить тип сортировки на расширенные функции, такие как сортировка на основе цвета ячейки.
Как сортировать данные в таблицах Excel (правильный способ)
- В первом поле необходимо задать начальное числовое значение, с которого продолжится заполнение (например, «1»);
- Во второй нужно задать следующее значение (например, «2»);
- Выделите обе области;
- Теперь при помощи маркера выделения отметьте нужную область таблицы, в которой необходимо сделать нумерацию.
Теперь я могу выбрать Amount billed во втором раскрывающемся списке. Комбинация этих двух правил начнется путем сортировки на основе имени клиента, а затем суммы, выставленного счёта за каждый проект.
Как все ячейки умножить на одно число?
- Сортировка данных всего в несколько кликов
- Установка нескольких, ступенчатых правил сортировки, таких как сортировка по алфавиту по состоянию, а затем по всё вместе.
- Создать полностью свои настройки сортировки, чтобы отсортировать данные с помощью любого установленного вами правила.
Самый простой способ снять ограничения прокрутки – сохранить, закрыть и снова открыть книгу. В качестве альтернативы, используйте шаги со 2 по 4 выше, чтобы открыть Свойства листа в окне VBA editor и удалить диапазон, указанный для прокрутки. Область свойство.
