Вытащить число из текста excel

Вытащить число из текста excel

Описание функции

Функция =ЧИСЛОИЗСТРОКИ(ТЕКСТ) извлекает число из текста ячейки. Причем не важно в какой части строки находится число: это может быть как начало или конец строки, так и ее середина. Функция имеет всего 1 аргумент:

  1. ТЕКСТ — строка или ссылка на ячейку, из которой необходимо извлечь число.

Функция умеет распознавать целые и дробные числа. К примеру, результатом работы по извлечению числа из ячейки, которая содержит текст "Арбузы за 20,5 рублей за кг", будет значение 20,5.

Для работы функции требуется установка надстройки VBA-Excel, после чего она будет добавлена в Excel и будет работает как и любая другая стандартная функция Excel.

Пример

На рисунке далее показаны результаты работы функции в различных случаях.

Внимание! Если текст ячейки содержит более одного числа, то функция их сольет воедино. Например, из текста "7 яблок и 3 груши" будет извлечено число 73.

Извлечем число из конца текстовой строки, например, из строки «Филатова123» получим «123».

Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР(A1)));1));0); 255)

Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).

Общая формула, позволяющая извлечь число из начала, середины и конца текстовой строки посложнее и выглядит так:

О построении этой формулы смотрите ]]> здесь ]]> . Это также формула массива — не забудьте нажать CRTL+SHIFT+ENTER .

Несколько чисел в текстовой строке (через WORD)

Если в текстовой строке расположено несколько чисел, то вышеуказанные формулы работать не будут. В этом случае можно предложить следующий подход:

  • скопируйте столбец с текстовыми строками, содержащие числа, в MS WORD;
  • нажмите CTRL+H , т.е. вызовите инструмент Найти и Заменить ;
  • В поле Найти введите ^$ (любая буква) или его выберите из меню (см. рисунок ниже);
Читайте также:  Найти ютуб открыть канал

  • в поле Заменить на: оставьте пустым (если все числа в строке нужно вывести одним числом, т.е. текстовая строка 123Филато11в6а будет преобразована в 123116 ) или введите пробел (если в дальнейшем потребуется вывести числа в отдельные ячейки);
  • нажмите ОК, буквы будут заменены пробелами или просто убраны;
  • скопируйте столбец обратно в MS EXCEL.

Если требуется вывести полученные числа в отдельные ячейки, то используйте инструмент Текст-по-столбцам (мастер текстов) или материал статьи Разнесение текстовых строк по столбцам.

Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)

Если в текстовой строке расположено несколько чисел, то для извлечения чисел можно вывести каждый символ текстовой строки в отдельную ячейку (см. файл примера, лист Общий случай ) для этого:

  • подсчитайте количество букв в текстовой строке (ячейка А3 ) с помощью функции ДЛСТР() в ячейке B3 ;
  • создайте табличку, состоящую из количества столбцов = количеству букв в текстовой строке;
  • заголовкам столбцов присвойте порядковые номера ;
  • в ячейку С3 введите формулу =ПСТР($A3;C$2;1) и протяните ее вправо, заполнив все столбцы.

Заменив формулу =ПСТР($A3;C$2;1) на =ЕСЛИ(ЕОШ(—ПСТР($A3;C$2;1));"";—ПСТР($A3;C$2;1)) можно вывести только числовые значения. Собрать все числовые значения в одну ячейку можно с помощью формулы =—Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7) или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0

Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)

Если в текстовой строке расположено несколько чисел и их нужно вывести в разные ячейки, то можно предложить следующий алгоритм (см. файл примера, лист Общий случай ):

  • как в предыдущем примере каждый символ текстовой строки выводим в отдельную ячейку;
  • с помощью функций СЖПРОБЕЛЫ() и ( СЦЕПИТЬ() или аперсанда &) выводим текстовую строку без букв (т.е. только числа), между числами — 1 пробел;
  • с помощью функции ПОИСК() находим начальные позиции каждого числа;
  • с помощью функции ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() выводим числа в отдельные ячейки.
Читайте также:  Официальная страница в инстаграмме

Решение из файла примера позволяет извлекать от 2-х до 4-х чисел из текстовых строк длиной до 15 символов. При желании решение можно легко расширить на большее количество символов и чисел.

Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.

Извлечение чисел из набора строк

Подробности Создано 20 Июль 2012

Вложения:

replace.xls [Извлечение чисел из текста] 14 kB

Небольшой пример по использованию операции замены текста, который часто встречается на практике. Требуется вырезать из набора однотипных строк текста числа для проведения дальнейших вычислений. Пример файла в приложении к статье.

Имеются ячейки с текстом, каждая из которых содержит внутри число. Для вычислений нужны только эти числа. Строки имеют похожий формат, но расположение чисел в тексте получается различным. Задача затрудняется еще тем, что использован неверный разделитель целой и дробной части: в строках это точка, русский Excel обычно предполагает запятую (зависит от региональных и внутренних настроек). На практике эта задача встречается довольно часто при копировании и вставке данных из внешних систем и файлов (1С, клиент-банк и прочее).

Неопытный пользователь, скорее всего, начнет просто вводить («перебивать») числа заново. Для предотвращения ошибок ввода можно попробовать заходить в редактирование формулы и копировать кусок текста. Однако проблему десятичной точки это не решит.

«Продвинутый» пользователь Excel может предложить использование формул для обработки текста. Пример реализации такой формулы приведен правее на том же листе.

Очевидно, что для написания и отладки этой формулы требуется затратить достаточно много времени. Возможно даже, что простой повторный ввод чисел будет быстрее на небольших массивах данных. Плюс метода заключатся в том, что формулу можно скопировать вниз или использовать повторно в другом файле.

Читайте также:  Можно перекинуть деньги с телефона на карту

Немногие знают, что подобная задача может быть решена простой операцией замены текста при помощи интерфейса Excel.

  1. Выделите диапазон A2:A11
  2. Нажмите Ctrl+H или любым другим способом вызовите диалог замены текста
  3. В поле "Найти" введите "*№* " (без кавычек, последний символ – пробел)
  4. Поле "Заменить на " оставьте пустым. Нажмите кнопку "Заменить все".
  5. Не закрывая диалог замены в поле "Найти" введите " *" (без кавычек, первый символ – пробел) . Нажмите кнопку "Заменить все".
  6. Не закрывая диалог замены в поле "Найти" введите "." (без кавычек, точка), в поле "Заменить на " введите "," (без кавычек, запятая). Нажмите кнопку "Заменить все".

Если все сделали верно, получится искомый массив чисел.

Символ "*" в строке поиска означает последовательность любых символов. Таким образом, в пункте 3 производился поиск подстроки до символа "№" и заканчивающейся на пробел. Затем просто вырезали найденную подстроку из текста (заменяли на пустую строку). В пункте 5 искали подстроку включающую в себя любые символы, но начинающуюся с пробела и также удаляли найденный текст.

Кроме символа "*", для поиска можно также использовать символ "?", он означает любой один символ. Эти символы работают как в диалоге замены, так и при поиске данных. Использованный метод обычно называют «поиском по маске».

Ссылка на основную публикацию
Вы не можете подключиться к общей папке
Многие столкнулись с тем, что при обновлении Windows 10 до последних релизов из под обновлённой ОС перестали открываться сетевые папки....
Видеокарта асус 1050 ti 4гб
Каждый заслуживает отличного игрового процесса. Вот почему ASUS создала видеокарту PH-GTX1050TI-4G. Теперь вы можете превратить свой ПК в игровой, на...
Видеокарта radeon hd 5770 1gb
Описание ATI начала продажи Radeon HD 5770 13 октября 2009 по рекомендованной цене 159$. Это десктопная видеокарта на архитектуре Terascale...
Вы хотите узнать дорогу
Упражнения 119 - 256 Вопрос Составьте предложения с обращениями к незнакомому человеку в следующих ситуациях: вы хотите узнать дорогу; вы...
Adblock detector