Извлечь число из текста excel
Как удалить из текстовых ячеек все символы, кроме цифр, таким образом «вытащив» из них цифры? Есть несколько простых и непростых решений.
Заменой остальных символов
Самым неоптимальным способом стала бы последовательная замена всех символов на «пустоту». Если лишних символов немного и вы умеете быстро печатать, можно обойтись и таким образом :) Но статья, конечно же, не о таких кейсах.
Если данных тысячи строк и лишних символов много, такой подход приведет к трате огромного количества времени.
Если данных сотни тысяч строк, и известно, что цифры присутствуют лишь в малой их части, будет полезным сперва найти числа в ячейках. Это позволит отфильтровать попадающие под требования ячейки и далее работать уже с ними — так будет менее ресурсозатратно. Возможно, далее вам потребуется удалить эти цифры из текста в ячейках Excel.
С помощью пользовательских функций (UDF)
Пример пользовательской функции, которая поможет с извлечением чисел из текста:
Как применить данный код:
— Внедрить его в новый модуль книги (используемой сейчас или в личной книге макросов)
— Применить функцию на листе в подобном виде: =extrNum(A1)
С помощью синтаксиса регулярных выражений
Регулярные выражения — отличный помощник при работе с текстовыми данными. Их синтаксис для обработки простых паттернов довольно прост, но возможностей все усложнить предостаточно.
Как в платной, так и в бесплатной версии !SEMTools есть функции:
- regexReplace, берущая на вход 3 аргумента — строку с данными, текст регулярного выражения и строку для замены.
- regexExtract с двумя аргументами — строкой и паттерном для извлечения.
Выражение для замены любых символов, кроме цифр, на пустоту, будет выглядеть следующим образом:
Выражение для извлечения первой сплошной последовательности цифр:
Извлечь цифры из текста в 1 клик
Пользователям платной версии !SEMTools доступна возможность извлекать цифры моментально прямо «на месте», не прописывая никаких функций.
Макрос находится в группе «ИЗВЛЕЧЬ» в меню «Извлечь символы».
Полезная особенность — макрос оставляет между числами пробел, если между ними был любой другой нецифровой символ. Это может помочь не склеить необратимо несколько чисел в одно. Если необходимости в этом нет, можно постфактум просто заменить пробел на «пустоту». Например, если в ячейке два номера телефона через запятую.
Видеоинструкция
Удалить текст, а цифры оставить — именно такова механика алгоритма !SEMTools. Смотрите короткий видеопример:
Извлечение цифр из ячеек со смешанным содержимым в !SEMTools
Нужно извлечь числа из текста в Excel?
В !SEMTools есть это и сотни других готовых решений!!
Иногда значения в ваших таблицах Excel выглядят как числа, но их нельзя сложить или перемножить, они приводят к ошибкам в формулах. Общая причина этого - числа, записанные как текст. Во многих случаях Microsoft Excel достаточно умен, чтобы автоматически преобразовывать цифровые символы, импортированные из других программ, в обычные числа. Но иногда числа остаются отформатированными в виде текста, что вызывает множество проблем в ваших электронных таблицах.
Перестает правильно работать сортировка данных, поскольку числовые и текстовые значения упорядочиваются по-разному. Функции поиска, подобные ВПР, также не могут найти нужные значения (подробнее об этом читайте – Почему не работает ВПР?). Подсчет по условиям СУММЕСЛИ и СЧЁТЕСЛИ даст неверные результаты. Если они находятся среди «нормальных» чисел, то функция СУММ их проигнорирует, а вы этого даже не заметите. В результате – неверные расчеты.
Из этого материала вы узнаете, как преобразовать строки в «настоящие» числа.
Как определить числа, записанные как текст?
В Excel есть встроенная функция проверки ошибок, которая предупреждает вас о возможных проблемах со значениями ячеек. Это выглядит как маленький зеленый треугольник в верхнем левом углу ячейки. При выборе ячейки с таким индикатором ошибки отображается предупреждающий знак с желтым восклицательным знаком (см. Скриншот ниже). Наведите указатель мыши на этот знак, и Excel сообщит вам о потенциальной проблеме: в этой ячейке число сохранено как текст или перед ним стоит апостроф .
В некоторых случаях индикатор ошибки не отображается для чисел, записанных в виде текста. Но есть и другие визуальные индикаторы текстовых чисел:
Число
Строка (текстовое значение)
-
• По умолчанию с выравниванием по правому краю.
• Если выбрано несколько ячеек, в строке состояния отображается «Среднее», «Количество» и «Сумма» .
-
• По умолчанию с выравниванием по левому краю.
• Если выбрано несколько ячеек, строка состояния показывает только Количество .
• В поле Числовой формат отображается текстовый формат (во многих случаях, но не всегда).
• В строке формул может быть виден начальный апостроф.
• Зелёный треугольник в левом верхнем углу.
На изображении ниже вы можете видеть текстовые представления чисел справа и реальные числа слева:
Есть несколько разных способов изменить текст на число Excel. Ниже мы рассмотрим их, начиная с самых быстрых и простых. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет проблем, которые невозможно преодолеть. Просто нужно попробовать другие способы.
Используем индикатор ошибок.
Если в ваших клетках отображается индикатор ошибки (зеленый треугольник в верхнем левом углу), преобразование выполняется одним щелчком мыши:
Таким образом можно одним махом преобразовать в числа весь столбец. Просто выделите всю проблемную область, а затем жмите восклицательный знак.
Смена формата ячейки.
Все ячейки в Экселе имеют определенный формат, который указывает программе, как их обрабатывать. Например, даже если в клетке таблицы будут записаны цифры, но формат выставлен текстовый, то они будут рассматриваться как простой текст. Никакие подсчеты с ними вы провести не сможете. Для того, чтобы Excel воспринимал цифры как нужно, они должны быть записаны с общим или числовым форматом.
Итак, первый быстрый способ видоизменения заключается в следующем:
Или же можно воспользоваться контекстным меню, вызвав его правым кликом мышки.
Последовательность действий в этом случае показана на рисунке. В любом случае, нужно применить числовой либо общий формат.
Этот способ не слишком удобен и достался нам «в наследство» от предыдущих версий Excel, когда еще не было индикатора ошибки в виде зелёного уголка.
Примечание. Этот метод не работает в некоторых случаях. Например, если вы примените текстовый формат, запишете несколько цифр, а затем измените формат на «Числовой». Тут ячейка все равно останется отформатированной как текст.
То же самое произойдёт, если перед цифрами будет стоять апостроф. Это однозначно указывает Excel, что записан именно текст и ничто другое.
Совет. Если зеленых уголков нет совсем, то проверьте - не выключены ли они в настройках вашего Excel (Файл - Параметры - Формулы - Числа, отформатированные как текст или с предшествующим апострофом).
Специальная вставка.
По сравнению с предыдущими методами этот метод требует еще нескольких дополнительных шагов, но работает почти на 100%.
- Выделите клетки таблицы с текстовым номером и установите для них формат «Общий», как описано выше.
- Скопируйте какую-нибудь пустую ячейку. Для этого либо установите в нее курсор и нажмите Ctrl + C , либо щелкните правой кнопкой мыши и выберите «Копировать» в контекстном меню.
- Выберите клетки таблицы, которые вы хотите трансформировать, щелкните правой кнопкой мыши и выберите «Специальная вставка». В качестве альтернативы, нажмите комбинацию клавиш Ctrl + Alt + V .
- В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить» и затем «Сложить» в разделе «Операция».
- Нажмите ОК.
Если все сделано правильно, то ваши значения изменят выравнивание слева на правую сторону. Excel теперь воспринимает их как числа.
Инструмент «текст по столбцам».
Это еще один способ использовать встроенные возможности Excel. При использовании для других целей, например для разделения ячеек, мастер «Текст по столбцам» представляет собой многоэтапный процесс. А вот чтобы просто выполнить нашу метаморфозу, нажимаете кнопку Готово на самом первом шаге :)
- Выберите позиции (можно и весь столбец), которые вы хотите конвертировать, и убедитесь, что их формат установлен на Общий.
- Перейдите на вкладку «Данные», группу «Инструменты данных» и нажмите кнопку «Текст по столбцам» .
- На шаге 1 мастера распределения выберите «Сразделителями» в разделе «Формат исходных данных» и сразу чтобы завершить преобразование, нажмите «Готово» .
Это все, что нужно сделать!
Повторный ввод.
Если проблемных ячеек, о которых мы ведём здесь разговор, у вас не очень много, то, возможно, неплохим вариантом будет просто ввести их заново.
Для этого сначала установите их формат на «Обычный». Затем в каждую из них введите цифры заново.
Думаю, вы знаете, как корректировать ячейку — либо двойным кликом мышки, либо через клавишу F2 .
Но это, конечно, если таких «псевдо-чисел» немного. Иначе овчинка не стоит выделки. Есть много других менее трудоемких способов.
Преобразовать текст в число с помощью формулы
До сих пор мы обсуждали встроенные возможности, которые можно применить для перевода текста в число в Excel. Во многих ситуациях это может быть сделано быстрее с помощью формулы.
В Microsoft Excel есть специальная функция - ЗНАЧЕН (VALUE в английском варианте). Она обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы для трансформирования.
Функция ЗНАЧЕН может даже распознавать набор цифр, включающих некоторые «лишние» символы.
Например, распознает цифры, записанные с разделителем тысяч в виде пробела:
Конвертируем число, введенное с символом валюты и разделителем тысяч:
Обе эти формулы возвратят число 1000.
Точно так же она расправляется с пробелами перед цифрами.
Чтобы преобразовать столбец символьных значений в числа, введите выражение в первую позицию и перетащите маркер заполнения, чтобы скопировать его вниз по столбцу.
Функция ЗНАЧЕН также пригодится, когда вы извлекаете что-либо из символьной строки с помощью одной из текстовых функций, таких как ЛЕВСИМВ, ПРАВСИМВ и ПСТР.
Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде цифр, используйте следующее:
На приведенном ниже рисунке продемонстрирована формула трансформации:
Если вы не обернете функцию ПРАВСИМВ в ЗНАЧЕН, результат будет возвращен в виде набора символов, что делает невозможным любые вычисления с извлеченными значениями.
Этот метод подходит, когда вы точно знаете, сколько символов и откуда вы желаете получить, а затем превратить их в число.
Математические операции.
Еще один способ - выполнить простую арифметическую операцию, которая фактически не меняет исходное значение. В этом случае программа, если есть такая возможность, сама сделает нужную конвертацию.
Что это может быть? Например, сложение с нулём, умножение или деление на 1.
Важно, чтобы эти действия не изменили величины чисел. Выше вы видите пример таких операций: двойное умножение на минус 1, умножение на 1, сложение с 0. Наиболее элегантно и просто для ввода выглядит «двойное отрицание»: ставим два минуса перед ссылкой, то есть дважды умножаем на минус 1. Результат расчета не изменится, а записать такую формулу очень просто.
Примечание. Если вы хотите, чтобы результаты были значениями, а не формулами, используйте после применения этого метода функцию специальной вставки, чтобы заменить их результатами.
Удаление непечатаемых символов.
Когда вы копируете в таблицу Excel данные из других приложений при помощи буфера обмена (то есть Копировать – Вставить), вместе с цифрами часто копируется и различный «мусор». Так в таблице могут появиться внешне не видимые непечатаемые символы. В результате ваши цифры будут восприниматься программой как символьная строка.
Эту напасть можно удалить программным путем при помощи формулы. Аналогично предыдущему примеру, в С2 можно записать примерно такое выражение:
Поясню, как это работает. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН, как мы уже говорили ранее, преобразует текст в число.
Макрос VBA.
Если вам часто приходится преобразовывать большие области данных из текстового формата в числовой, то есть резон для этих повторяющихся операций создать специальный макрос, который будет использоваться при необходимости. Но для того, чтобы это выполнить, прежде всего, нужно в Экселе включить макросы и панель разработчика, если это до сих пор не сделано. Нажмите правой кнопкой мыши на ленте и настройте показ этого раздела.
Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert - Module и скопируйте туда следующее небольшое выражение:
После этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.
Что делает этот макрос?
Вы можете выделить несколько областей данных для конвертации (можно использовать мышку при нажатой клавише CTRL). При этом, если в ваших числах в качестве разделителя десятичных разрядов используется запятая, то она будет автоматически заменена на точку. Ведь в Windows чаще всего именно точка отделяет целую и дробную части числа. А при экспорте данных из других программ запятая в этой роли встречается почему-то очень часто.
Чтобы использовать этот код, выделяем область на рабочем листе, которую нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.
Открывается окно имеющихся макросов. Находим «Текст_в_число», указываем на его и жмем на кнопку «Выполнить».
Извлечь число из текстовой строки с помощью Ultimate Suite
Как вы уже убедились, не существует универсальной формулы Excel для извлечения числа из текстовой строки. Если у вас возникли трудности с пониманием формул или их настройкой для ваших наборов данных, вам может понравиться этот простой способ получить число из строки в Excel.
Надстройка Ultimate Suite предоставляет множество инструментов для работы с текстовыми значениями: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать символы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить его по отдельным ячейкам, заменить ошибочные символы с правильными.
Вот как вы можете быстро получить число из любой буквенно-цифровой строки:
- Перейдите на вкладку AblebitsData >Текст и нажмите Извлечь (Extract) :
- Выделите все ячейки с нужным текстом.
- На панели инструмента установите переключатель «Извлечь числа (Extractnumbers)».
- В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите переключатель «Вставить как формулу (Insertasformula)» или оставьте его неактивным (по умолчанию).
Я советую активировать эту возможность, если вы хотите, чтобы извлеченные числа обновлялись автоматически, как только в исходные строки вносятся какие-либо изменения.
Если вы хотите, чтобы результаты не зависели от исходных строк (например, если вы планируете удалить исходные данные позже), не выводите результат в виде формулы.
Как и в предыдущем примере, результаты извлечения являются числами , что означает, что вы можете подсчитывать, суммировать, усреднять или выполнять любые другие вычисления с ними.
В этом примере мы решили вставить результаты как формулы , и надстройка сделала именно то, что было запрошено:
Сложновато самому написать такую формулу, не правда ли?
Если отсутствует флажок «Вставить как формулу», вы увидите число в строке формул.
Любопытно попробовать? Просто скачайте пробную версию Ultimate Suite и убедитесь сами :)
Если вы хотите иметь этот, а также более 60 других полезных инструментов в своем Excel, воспользуйтесь этой специальной возможностью покупки, которую предоставлена исключительно читателям нашего блога.
Вот как вы можете преобразовать текст в число Excel с помощью формул и встроенных функций. Более сложные случаи, когда в ячейке находятся одновременно и буквы, и цифры, мы рассмотрим в отдельной статье. Я благодарю вас за чтение и надеюсь не раз еще увидеть вас в нашем блоге!
Как быстро посчитать количество слов в Excel - В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как умножить число на процент и прибавить проценты - Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения. Чему равен процент от числаКак умножить число на процентКак…
Как считать проценты в Excel — примеры формул - В этом руководстве вы познакомитесь с быстрым способом расчета процентов в Excel, найдете базовую формулу процента и еще несколько формул для расчета процентного изменения, процента от общей суммы и т.д.…
Округление в Excel – какую из 12 формул лучше использовать? - В статье объясняется использование ОКРУГЛ, ОКРУГЛТ, ОКРУГЛВВЕРХ, ОКРВНИЗ, ЦЕЛОЕ, ОТБР и множества других функций округления в Excel. Также приводятся примеры формул для изменения десятичных чисел до целых или до определенного…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.
Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.
Способ 1. Формулы
Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT) :
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT) :
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена:
В английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов - подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
Способ 2. Макрофункция
Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно - написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно - искать заданную подстроку в строке в обратном направлении - от конца к началу.
Нажмите сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь можно сохранить книгу (в формате с поддержкой макросов!) и воспользоваться созданной функцией в следующем синтаксисе:
=LastWord(txt ; delim ; n)
- txt - ячейка с исходным текстом
- delim - символ-разделитель (по умолчанию - пробел)
- n - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)
При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа Excel.
Способ 3. Power Query
Power Query - это бесплатная надстройка от Microsoft для импорта данных в Excel из практически любых источников и последующей трансформации загруженных данных в любой вид. Мощь и крутизна этой надстройки настолько велики, что Microsoft встроила все ее возможности в Excel 2016 по умолчанию. Для Excel 2010-2013 Power Query можно бесплатно скачать отсюда.
Наша задача по отделению последнего слова или фрагмента через заданный разделитель с помощью Power Query решается очень легко.
Сначала превратим нашу таблицу с данными в умную с помощью сочтания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) :
Затем загрузим созданную "умную таблицу" в Power Query с помощью команды Из таблицы / диапазона (From table/range) на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013):
В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:
После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete ) . Также можно переименовать оставшийся столбец в шапке таблицы.
Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close & Load - Close & Load to. ) :
И в итоге получаем:
Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)
Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 обновить наш запрос.
В этом руководстве представлены три метода извлечения чисел из строки смешанного буквенно-цифрового текста в Excel.
The Kutools for Excel's EXTRACTNUMBERS utility helps you easily extract all numbers from mixed alphanumeric text string in Excel. See below screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More Download the free trial now
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Извлечение чисел из смешанных буквенно-цифровых текстовых строк с помощью формулы
Вы можете применить приведенную ниже формулу для извлечения чисел из любого места текстовой строки в Excel.
1. Выберите пустую ячейку для вывода извлеченных чисел, введите в нее формулу ниже и нажмите Enter ключ. Затем перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам.
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, , "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")
Внимание: В формуле A1 - это ячейка, в которой находится текстовая строка, из которой вы хотите извлечь числа. Пожалуйста, измените его в соответствии с вашими потребностями.
Затем числа извлекаются из любого места текстовой строки в указанных ячейках, как показано на скриншоте выше.
Сохраняйте числа только в смешанных буквенно-цифровых текстовых строках с кодом VBA
Следующий код VBA может помочь вам удалить все нечисловые символы из указанного диапазона и сохранить только числа в ячейках. Пожалуйста, сделайте следующее.
Внимание: Поскольку код напрямую исключает исходный диапазон, сохраните копию исходного диапазона на случай потери данных.
1. Нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модули. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.
Код VBA: удалить все нечисловые символы из ячеек
3. нажмите F5 ключ для запуска кода. Потом Kutools for Excel появится диалоговое окно, выберите диапазон, содержащий смешанные буквенно-цифровые текстовые строки, вы оставите только числа, и нажмите кнопку OK кнопку.
Теперь все нечисловые символы в выбранном диапазоне были немедленно удалены, и остались только числа. Смотрите скриншот:
Легко извлекайте числа из смешанных буквенно-цифровых текстовых строк с помощью Kutools for Excel
В этом разделе рекомендуется утилита извлечения текста из Kutools for Excel. С помощью этой утилиты можно легко обрабатывать извлечение чисел из текстовой строки.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Кутулс > Текст > Извлечь текст для включения функции.
2. в Извлечь текст диалоговое окно, выполните следующие настройки.
2.2). Диапазон , нажмите кнопка для выбора диапазона ячеек, из которого будет извлекаться текст;
Примечание: Проверить Вставить как формулу поле в нижнем левом углу диалогового окна вставит результат в виде формулы в ячейки результатов. Когда значение указанной ячейки изменяется, результат обновляется автоматически.
3. в Извлечь текст диалоговом окне выберите пустую ячейку для вывода извлеченного текста, а затем щелкните ОК.
Затем извлекаются числа из любой позиции в текстовой строке. Смотрите скриншот:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Вы когда-нибудь хотели извлекать числа только из списка строк в Excel? Здесь я познакомлю вас с некоторыми способами быстрого и легкого извлечения только чисел в Excel.
Метод 1. Извлечь число только из текстовых строк с формулой
Следующая длинная формула может помочь вам извлечь только числа из текстовых строк, пожалуйста, сделайте следующее:
Выберите пустую ячейку, в которую вы хотите вывести извлеченное число, затем введите эту формулу: = СУММПРОИЗВ (СРЕДНЯЯ (0 & A5; НАИБОЛЬШИЙ (ИНДЕКС (- СРЕДНЕЕ (A5; СТРОКА (КОСВЕННАЯ ("1:" & LEN (A5))); 1)) * СТРОКА (КОСВЕННАЯ ("1:" & LEN (A5) )), 0), СТРОКА (КОСВЕННАЯ ("1:" & LEN (A5)))) + 1, 1) * 10 ^ ROW (INDIRECT ("1:" & LEN (A5))) / 10) , а затем перетащите маркер заполнения, чтобы заполнить диапазон, необходимый для применения этой формулы. Смотрите скриншот:
Ноты:
- 1. A5 стоит первые данные, из которых вы хотите извлечь числа только из списка.
- 2. Если в строке нет чисел, результат будет показан как 0.
Извлекать числа только из текстовых строк:
Работы С Нами Kutools for ExcelАвтора ВЫДЕРЖКИ функция, вы можете быстро извлекать только числа из ячеек текстовой строки. Нажмите, чтобы загрузить Kutools for Excel!
Метод 2: извлекать число только из текстовых строк с кодом VBA
Вот код VBA, который также может оказать вам услугу, пожалуйста, сделайте следующее:
1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: извлекать номер только из текстовой строки:
3. А затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, напоминающее о выборе текстового диапазона, который вы хотите использовать, см. снимок экрана:
4, Затем нажмите OK, следующее окно подсказки, выберите ячейку для вывода результата, см. снимок экрана:
5, Наконец, нажмите OK кнопку, и все числа в выбранных ячейках были извлечены сразу.
Метод 3: извлечь номер только из текстовой строки с помощью Kutools for Excel
Kutools for Excel также имеет мощную функцию, которая называется ВЫДЕРЖКИ, с помощью этой функции вы можете быстро извлечь только числа из исходных текстовых строк.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Щелкните ячейку помимо текстовой строки, в которую вы поместите результат, см. Снимок экрана:
2. Затем нажмите Кутулс > Функции Kutools > Текст > ВЫДЕРЖКИ, см. снимок экрана:
3. В Аргументы функций диалоговом окне выберите ячейку, в которой вы хотите извлечь числа из Текст текстовое поле, а затем введите правда or ложный в N текстовое поле, см. снимок экрана:
Внимание: Аргумент N является необязательным элементом, если вы введете правда, он вернет числа как числовые, если вы введете ложный, он вернет числа в текстовом формате, значение по умолчанию - false, поэтому вы можете оставить его пустым.
4, Затем нажмите OK, числа были извлечены из выбранной ячейки, затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите применить к этой функции, вы получите следующий результат:
Method 4:Split text string into text and number columns individually with Kutools for Excel
If you want to split the text string into separated text and number columns, Kutools for Excel’s Split Cells also can help you to solve this task.
After installing Kutools for Excel , please do as follows:
1. Select the text string that you want to split, and then click Kutools > Text > Split Cells, see screenshot:
2. In the Split Cells dialog box, select Split to Columns under the Type section, and then check Text and number from the Split by section, see screenshot:
3. And then click Ok button, select a cell to put the result in the popped out dialog box, see screenshot:
4. Then click OK button, and the text strings have been split into separated text and number columns as following screenshot shown:
Метод 4: извлечь десятичное число только из текстовой строки с формулой
Если текстовые строки содержат некоторые десятичные числа на вашем листе, как вы могли бы извлечь из текстовых строк только десятичные числа?
Приведенная ниже формула может помочь вам быстро и легко извлечь десятичные числа из текстовых строк.
Введите эту формулу : =LOOKUP(9.9E+307,--LEFT(MID(A5,MIN(FIND(, $A5&"1023456789")),999),ROW(INDIRECT("1:999")))) ,, А затем заполните дескриптор до ячеек, которые вы хотите содержать эту формулу, все десятичные числа были извлечены из текстовых строк, см. Снимок экрана:
Извлечь номер только из строк с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: