Как отделить цифры от текста в эксель
В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое.
Среди множества различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВСИМВ - одна из наиболее широко применяемых. Как следует из названия, она позволяет извлекать определенное количество знаков, начиная с левой стороны текста. Однако она способна на гораздо большее, чем такая простая операция. В этом руководстве вы найдете несколько базовых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать ЛЕВСИМВ далеко за пределами ее базового применения.
Cинтаксис.
Функция ЛЕВСИМВ в Excel возвращает указанное количество символов (подстроку) от начала содержимого ячейки.
ЛЕВСИМВ (текст; [колич_знаков])
- Текст (обязательно) - это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) - количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Например, чтобы извлечь первые 6 символов из A2, запишите такое выражение:
На следующем скриншоте показан результат:
Важное замечание! ЛЕВСИМВ относится к категории текстовых функций, поэтому результатом её всегда является текстовая строка, даже если исходное значение, из которого вы извлекаете цифры, является числом. Если вы работаете с числовым набором данных и хотите, чтобы было извлечено именно число, применяйте ее вместе с функцией ЗНАЧЕН, как показано в одном из примеров ниже.
Что еще можно делать, помимо извлечения текста слева? В следующих примерах показано, как можно применять её в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом.
В некоторых случаях может потребоваться извлечь часть текста, который предшествует определенному знаку. Например, вы можете извлечь имена из столбца ФИО или получить коды стран из колонки с телефонными номерами. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому вы не можете просто указать точное число сколько знаков отделить, как мы сделали в приведенном выше примере.
Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .
Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(" "; A2)). А теперь вы вставляете это выражение в ЛЕВСИМВ:
Чтобы еще немного улучшить результат, избавьтесь от конечного пробела, вычтя 1 из результата поиска. Ведь невидимые конечные пробелы могут вызвать множество проблем, особенно если вы планируете использовать извлеченные имена в других вычислениях:
Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса ("-"), а не пробела:
Думаю, вы поняли, что можете брать эту универсальную формулу, чтобы получить подстроку, которая предшествует любому другому знаку:
Как удалить последние N символов.
Вы уже знаете, отрезать кусочек от начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов из конца строки и перенести оставшееся в другую ячейку. Для этого можно также применять функцию ЛЕВСИМВ в сочетании с ДЛСТР , например:
ЛЕВСИМВ ( текст ; ДЛСТР( текст ) - число_символов_для_удаления )
Это работает с такой логикой: ДЛСТР получает общее количество символов в ячейке, затем вы вычитаете количество ненужных знаков из общей длины, а ЛЕВСИМВ возвращает оставшееся.
Например, чтобы удалить последние 7 знаков из текста в A2, запишите такое выражение:
Как показано на скриншоте, формула успешно отсекает слово «продукты» (8 букв, разделитель и 2 пробела) из текстовых значений в столбце A.
При этом взять на вооружение способ, рассмотренный нами в предыдущем примере, будет очень сложно, так как все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы уже знаете, ЛЕВСИМВ в Эксель всегда возвращает текст, даже если вы извлекаете несколько первых цифр из ячейки. Для вас это означает, что вы не сможете использовать эти результаты в вычислениях или в других функциях Excel, которые работают с числами.
Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь символы перед разделителем “-” из A2 и преобразовать результат в число, можно сделать так:
Результат будет выглядеть примерно так:
Как вы можете видеть на скриншоте выше, числа в столбце B, полученные с помощью преобразования, автоматически выровнены по правому краю в ячейках, в отличие от текста с выравниванием по левому краю в столбце A. Поскольку Эксель распознает итоговые данные как числа, вы можете суммировать и усреднять эти значения, находить минимальное и максимальное значение и выполнять любые другие вычисления с ними.
Это лишь некоторые из множества возможных вариантов использования ЛЕВСИМВ в Excel.
Дополнительные примеры формул ЛЕВСИМВ можно найти на следующих ресурсах:
Не работает ЛЕВСИМВ — причины и решения
Если ЛЕВСИМВ не работает на ваших листах должным образом, это, скорее всего, связано с одной из причин, которые мы перечислим ниже.
1. Аргумент «количество знаков» меньше нуля
Чтобы лучше проиллюстрировать эту мысль, возьмем формулу, которую мы записали в первом примере для извлечения телефонных кодов страны:
ЛЕВСИМВ(A2; ПОИСК("-"; A2)-1)
2. Начальные пробелы в исходном тексте
Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, довольно часто такие неприятные сюрпризы попадаются в самом начале текста. И вы вряд ли заметите, что они там есть, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от ведущих пробелов на листах, воспользуйтесь СЖПРОБЕЛЫ (TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать ЛЕВСИМВ для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как числа, представляющие количество дней с 1 января 1900 года. То, что вы видите в ячейке, это просто визуальное представление даты. Ее отображение можно легко изменить, применив другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день с помощью выражения ЛЕВСИМВ(A1;2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое представляет 15 июля 2020г. во внутренней системе Эксель.
Чтобы извлечь определенную часть даты, возьмите одну из следующих функций: ДЕНЬ(), МЕСЯЦ() или ГОД().
Если же ваши даты вводятся в виде текстовых строк, то ЛЕВСИМВ будет работать без проблем, как показано в правой части скриншота:
Вот как можно использовать функцию ЛЕВСИМВ в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel вы можете выполнить при помощи специального инструмента работы с текстом, включённого в надстройку Ultimate Suite. Вот только некоторые из этих возможностей: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать буквы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить по ячейкам, извлечь отдельные слова, найти дубликаты слов.
Как быстро посчитать количество слов в Excel - В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
Как преобразовать текст в число в Excel — 10 способов. - В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…
Для массового удаления из диапазона ячеек всех цифр разом в стандартном арсенале Excel функций и процедур, к сожалению не предусмотрено. Есть несколько не самых изящных, но рабочих решений, которые и рассмотрим.
Если же задача — удалить не все цифры, а только отдельно стоящие, их еще можно назвать словами из одних цифр, то задача превращается в нереализуемую вовсе. С помощью надстройки !SEMTools можно — но об этом далее. Сначала рассмотрим обычное удаление цифр.
Обратите внимание — есть похожая процедура «извлечь числа из текста» — она позволит, наоборот, оставить только цифры, а все остальное удалить. А если массив данных огромен, быстрее будет сначала найти числа в ячейках, чтобы потом запустить процедуру удаления только в найденных, предварительно отсортировав их.
Удаление цифр последовательной заменой
Обычно пользователи пользуются функционалом поиска и замены, повторяя операцию 10 раз для каждой цифры и заменяя их на пустоту.
Можно автоматизировать процесс, записав его с помощью макрорекордера, но это не очень удобно — процедура замены требует значительных ресурсов и занимает значительное время на больших данных.
Формула для удаления цифр из ячейки
В Excel есть функция «ПОДСТАВИТЬ«, она позволяет заменить символ или несколько символов, встречающиеся в ячейке, полностью, вне зависимости от того, сколько раз они встречаются в строке.
На ее основе можно составить многосоставную формулу с 10 уровнями вложенности, которая решит нашу задачу. Так она будет выглядеть:
Автору этого сайта и надстройки !SEMTools часто приходилось решать эту и множество похожих задач, поэтому у него был огромный текстовый файлик с такими формулами. Его приходилось всегда иметь на виду, чтобы быстро использовать нужные. Это было неудобно.
Так пришла идея реализовать возможность производить такие операции в 1 клик и легко находить их. И теперь она есть, в составе целого пакета из 500+ похожих процедур надстройки !SEMTools.
Удалить цифры из текста ячеек в 1 клик
Процедура доступна на панели !SEMTools в разделе «УДАЛИТЬ» в меню «Удалить символы«. Перед удалением рекомендуется воспользоваться похожей функций «Найти числа в ячейках«, чтобы сначала посмотреть, какие ячейки затронет удаление. Смотрите пример:
Удалить слова, содержащие цифры
Как видно из примера выше, если цифры являются частью слов, в которых присутствуют и буквы, это повредит такие слова и не удалит их полностью. Для удаления таких слов в !SEMTools предусмотрены процедуры в меню «Удалить слова«:
- удалить слова, содержащие цифры
- удалить слова, состоящие только из цифр
Это позволяет удалять их полностью и не «испортить» данные.
Скачайте надстройку !SEMTools, чтобы упростить эту и сотни других задач в работе с Excel!
Как удалить из текстовых ячеек все символы, кроме цифр, таким образом «вытащив» из них цифры? Есть несколько простых и непростых решений.
Заменой остальных символов
Самым неоптимальным способом стала бы последовательная замена всех символов на «пустоту». Если лишних символов немного и вы умеете быстро печатать, можно обойтись и таким образом :) Но статья, конечно же, не о таких кейсах.
Если данных тысячи строк и лишних символов много, такой подход приведет к трате огромного количества времени.Если данных сотни тысяч строк, и известно, что цифры присутствуют лишь в малой их части, будет полезным сперва найти числа в ячейках. Это позволит отфильтровать попадающие под требования ячейки и далее работать уже с ними — так будет менее ресурсозатратно. Возможно, далее вам потребуется удалить эти цифры из текста в ячейках Excel.
С помощью пользовательских функций (UDF)
Пример пользовательской функции, которая поможет с извлечением чисел из текста:
Как применить данный код:
— Внедрить его в новый модуль книги (используемой сейчас или в личной книге макросов)
— Применить функцию на листе в подобном виде: =extrNum(A1)С помощью синтаксиса регулярных выражений
Регулярные выражения — отличный помощник при работе с текстовыми данными. Их синтаксис для обработки простых паттернов довольно прост, но возможностей все усложнить предостаточно.
Как в платной, так и в бесплатной версии !SEMTools есть функции:
- regexReplace, берущая на вход 3 аргумента — строку с данными, текст регулярного выражения и строку для замены.
- regexExtract с двумя аргументами — строкой и паттерном для извлечения.
Выражение для замены любых символов, кроме цифр, на пустоту, будет выглядеть следующим образом:
Выражение для извлечения первой сплошной последовательности цифр:
Извлечь цифры из текста в 1 клик
Пользователям платной версии !SEMTools доступна возможность извлекать цифры моментально прямо «на месте», не прописывая никаких функций.
Макрос находится в группе «ИЗВЛЕЧЬ» в меню «Извлечь символы».
Полезная особенность — макрос оставляет между числами пробел, если между ними был любой другой нецифровой символ. Это может помочь не склеить необратимо несколько чисел в одно. Если необходимости в этом нет, можно постфактум просто заменить пробел на «пустоту». Например, если в ячейке два номера телефона через запятую.
Видеоинструкция
Удалить текст, а цифры оставить — именно такова механика алгоритма !SEMTools. Смотрите короткий видеопример:
Извлечение цифр из ячеек со смешанным содержимым в !SEMTools
Нужно извлечь числа из текста в Excel?
В !SEMTools есть это и сотни других готовых решений!!Порой при работе в 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 клика
Для тех, кто ценит время, я разработал процедуру, которая разбивает текст и цифры прямо внутри ячейки, или выводит результат такой процедуры в соседний столбец — это зависит от глобальной настройки вывода результатов.
Все, что вам нужно сделать, это:
- просто выделить диапазон или ячейку,
- вызвать процедуру,
- указать разделитель
- и нажать ОК.
Отделить цифры от текста в Excel, а также решить сотни других рутинных задач поможет надстройка !SEMTools
Скачайте надстройку прямо сейчас и начинайте экономить рабочее время!Разделить текст и числа в ячейке в Excel
Предположим, что данные ячейки смешаны с текстом и числами, как вы можете разделить их на отдельные ячейки столбца? Этот туториал покажет вам подробные шаги, чтобы разобраться с формулами.
Как разделить текст и числа в ячейке в Excel?
В этом случае вам необходимо применить три формулы для разделения текста и чисел в ячейке на отдельные ячейки столбца.
Во-первых, вам нужно определить позицию первого числа;
Во-вторых, извлечь текст из ячейки;
Наконец, получите числа.Общие формулы
Общая формула для определения позиции первого числа
Общая формула для получения текста
Общая формула для получения чисел
Аргументы приведенных выше формул
A1: Представляет ячейку, содержащую текст и числа, которые вы хотите разделить;
Позиция: Позиция первого числа в A1.
Как пользоваться этими формулами?
Предположим, у вас есть список текстовых строк, как показано на скриншоте ниже, вы можете сделать следующее, чтобы разделить текст и числа на отдельные ячейки.
Во-первых, верните начальную позицию чисел в текстовой строке
1. Выберите пустую ячейку для вывода позиции. В данном случае я выбираю C3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите ячейку результата, перетащите маркер заполнения вниз, чтобы применить формулу к другой ячейке, пока не отобразятся все нужные позиции.
Во-вторых, получить текст
1. Выберите пустую ячейку, чтобы найти текст. Здесь я выбираю D3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите эту ячейку результата, перетащите маркер заполнения вниз, чтобы получить все тексты ячеек ниже.
=LEFT(B3,C3-1)
Наконец, получите числа
1. Выберите пустую ячейку, например E3, чтобы найти числа.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите эту ячейку результата, перетащите маркер заполнения вниз, чтобы получить все номера ячеек ниже.
=RIGHT(B3,LEN(B3)-C3+1)
Как работают эти формулы?
- B3&"0123456789" возвращает результат как: ana varela 240.150123456789.
- FIND(, "ana varela 240.150123456789") : Функция Find находит позицию цифр «0–9» в ana varela 240.150123456789 и возвращает результат как .
Результат означает, что число 0 находится на 14-й позиции в ана варела 240.150123456789, число 1 находится на 16 позиции в ана варела 240.150123456789…
2. =MIN() : Функция MIN получает минимальное значение массива. Здесь результат 12.
=LEFT(B3,C3-1)
LEFT(ana varela 240.15,12-1) это означает, что функция LEFT извлекает первые 11 символов из левой части текстовой строки «ana varela 240.15». Конечный результат - ана варела.
=RIGHT(B3,LEN(B3)-C3+1)
1. LEN(B3)-C3+1 :
- Функция LEN вычисляет общую длину текстовой строки (ana varela 240.150123456789) в B3 и возвращает результат как 17.
- 17-12 + 1: Общая длина чисел в ана варела 240.15. Результат - 6.
2. =RIGHT(B3,6) : Функция ВПРАВО извлекает 6 символов из правой части ana varela 240.15. И окончательный результат - 240.15.
Связанные функции
Функция Excel MID
Функция Excel MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.Функция поиска в Excel
Функция Excel FIND используется для поиска строки внутри другой строки и возвращает начальную позицию строки внутри другой строки.Функция ВЛЕВО в Excel
Функция Excel LEFT извлекает заданное количество символов из левой части предоставленной строки.Функция Excel ВПРАВО
Функция Excel RIGHT извлекает определенное количество символов из правой части текстовой строки.Функция ДЛСТР в Excel
Функция Excel LEN возвращает количество символов в текстовой строке.Родственные формулы
Разделение размеров на две части в Excel
В этой статье объясняется, как использовать формулу для разделения размеров в ячейке на две части без единиц измерения (индивидуальная длина и ширина).Отделяйте числа от единиц измерения
В этой статье объясняется, как использовать формулу для отделения чисел от единиц измерения.Разделить текстовую строку на определенный символ в ячейке в Excel
В этом руководстве подробно объясняется, как разбить текстовую строку по определенному символу в ячейке с формулами.Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Читайте также: