Как быстро извлечь число из текста в Excel
В этом кратком руководстве показано, как быстро извлечь число из различных текстовых выражений в Excel с помощью формул или специального инструмента для извлечения».
Проблема извлечения числа из текста возникает довольно часто, особенно при работе с данными, полученными из других программ. Например, вам нужно извлечь почтовый индекс из адреса, номер склада из строки с названием товара, номер счета из платежного документа. Нужное нам число может находиться в любом месте текста: в начале, в середине или в конце.
Когда дело доходит до извлечения части текстового значения определенной длины, Excel предоставляет три текстовые функции (LEFT, RIGHT и MID) для быстрого выполнения этой задачи. Когда дело доходит до извлечения числа из буквенно-цифровой строки, Microsoft Excel . ничего не дает.
Извлечение числа из текста в Excel требует немного изобретательности, немного терпения и множества различных функций, вложенных друг в друга.
Или вы можете запустить дополнительный инструмент Extract from Ablebits Ultimate Suite и сделать это одним щелчком мыши. Ниже вы найдете полную информацию обо всех этих методах.
Как извлечь число из конца текстовой строки.
Если у вас есть столбец буквенно-цифровых значений, в котором число всегда идет после текста, вы можете использовать одну из следующих формул для извлечения из них чисел.
Важная заметка! В следующих формулах извлечение выполняется с помощью функций ВПРАВО и ВЛЕВО, которые относятся к категории текстовых функций. Эти функции всегда возвращают текст. В нашем случае результатом будет числовая подстрока, которая, с точки зрения Excel, также является текстом, а не числом. Если результатом должно быть число (которое можно использовать в дальнейших вычислениях), оберните соответствующую формулу в функцию ЗНАЧЕНИЕ или выполните с ней простейшую математическую операцию (например, двойное отрицание).
Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с чего начать операцию. Затем мы определяем положение первой цифры, используя это общее выражение:
= МИН (ПОИСК ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; ячейка & «0123456789»))
О логике расчетов поговорим чуть позже. А пока просто замените слово «ячейка» ссылкой на позицию, содержащую исходный текст (в нашем случае, A2), и запишите полученное выражение в любой пустой ячейке той же строки, например, в B2:
= МИН (ПОИСК ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A2 & «0123456789»))
Хотя формула содержит константу массива, это обычное выражение, которое вводится обычным способом: нажатием клавиши Enter.
После того, как вы определили положение первой цифры, вы можете использовать функцию ВПРАВО, чтобы получить номер. Чтобы узнать, сколько символов нужно извлечь, вычтите позицию первой цифры из общей длины строки и добавьте единицу к результату, потому что первая цифра также должна быть включена:
= ВПРАВО (A2; DLSTR (A2) -B2 + 1)
Где A2 — исходная ячейка, а B2 — позиция первой цифры.
На следующем снимке экрана показаны результаты:
Чтобы исключить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу MIN непосредственно в функцию RIGHT следующим образом:
= ПРАВО (A2; DLSTR (A2) -MIN (ПОИСК ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A2 & «0123456789»)) + 1)
Чтобы формула возвращала число вместо числовой строки, заключите его в функцию VALUE:
= ЗНАЧЕНИЕ (ПРАВО (A2; DLSTR (A2) -MIN (ПОИСК ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A2 & «0123456789»)) + 1))
Или просто двойное отрицание с двумя знаками минус»:
= — ВПРАВО (A2; DLSTR (A2) -MIN (ПОИСК ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A2 & «0123456789»)) + 1)
Другой способ извлечь число из конца строки — использовать такое выражение:
= ПРАВО (ячейка; СУММ (DLSTR (ячейка) — DLSTR (ПОДСТАВИТЬ (ячейка; {«0»; «1»; «2»; «3»; «4»; «5»; «6»; «7» ; «8»; «9»};»»))))
Используя исходный текст в ячейке A2, напишите следующую формулу в ячейке B2 или любой другой пустой ячейке в той же строке, а затем скопируйте ее в столбец:
= ПРАВО (A2; СУММ (DLSTR (A2) — DLSTR (ПОДСТАВИТЬ (A2; {«0»; «1»; «2»; «3»; «4»; «5»; «6»; «7» ; «8»; «9»};»»))))
Примечание. Эти формулы предназначены для случая, когда числа находятся только в конце текстовой строки. Если какие-то числа стоят даже в середине или в начале, ничего не получится.
Этих недостатков лишена третья формула, которая извлекает только последнее число в тексте, игнорируя все предыдущие:
= RIGHT (A2; DLSTR (A2) — MAX (IF (ISNUMBER (MID (A2; STRING (INDIRECT («1:» & DLSTR (A2))))); 1) * 1)) = FALSE; STRING (INDIRECT («1:» & DLSTR (A2))); 0)))
На скриншоте ниже вы можете увидеть результат его работы.
Как видите, числа в начале или в середине текста игнорируются. Также обратите внимание, что результат, как и в предыдущих формулах, представляет собой число, записанное в виде текста. О том, как превратить его в обычный номер, мы уже рассказывали выше в этой статье.
Примечание. Если вы используете Excel 2019 или более раннюю версию, вам нужно использовать формулу массива, нажимая Ctrl + Shift + Enter при вводе. Если у вас Office365, введите как обычно с помощью Enter.
Как извлечь число из начала текстовой строки
Если вы работаете со строками, в которых текст идет после числа, решение для извлечения числа будет аналогично описанному выше. Единственное отличие состоит в том, что вы используете функцию LEFT для извлечения из левой части текста:
= LEFT (ячейка; СУММ (DLSTR (ячейка) -LSTR (ПОДСТАВИТЬ (ячейка; {«0»; «1»; «2»; «3»; «4»; «5»; «6»; «7» ; «8»; «9»};»»))))
Используя этот метод для A2, мы извлекаем число, используя это выражение:
= ЛЕВЫЙ (A2; СУММ (DLSTR (A2) -LSTR (ПОДСТАВИТЬ (A2; {«0»; «1»; «2»; «3»; «4»; «5»; «6»; «7» ; «8»; «9»};»»))))
Это решение работает для текстовых выражений, содержащих только числа в начале. Если некоторые числа находятся даже в середине или в конце строки, формула не будет работать.
Если вы хотите извлечь только числа слева и игнорировать остальные, используйте другую формулу:
= ЛЕВЫЙ (LA2; ПОИСК (ЛОЖЬ; ЕЧИСЛО (- СРЕДНЕЕ (A2; СТРОКА ($ 1: $ 94); 1)); 0) -1)
Или немного модифицируем, чтобы ускорить расчеты:
= ЛЕВЫЙ (A2; ПОИСК (ЛОЖЬ; ЕЧИСЛО (СРЕДНЕЕ (A2; СТРОКА (КОСВЕННАЯ («1:» & DLSTR (A2) +1))); 1) * 1); 0) -1)
Если у вас есть Excel 2019 и более ранние версии, введите его как формулу массива, используя Ctrl + Shift + Enter. В Office365 и более поздних версиях вы можете войти в систему как обычно.
Примечание. Как и функция RIGHT, функция LEFT также возвращает числовую подстроку, которая технически является текстом, а не числом.
Как получить число из любой позиции в тексте
Если ваш бизнес предполагает извлечение числа из любого места строки, вы можете использовать следующую формулу:
= СУММПРОИЗВ (СРЕДНИЙ (0 & A2; НАИБОЛЬШИЙ (ИНДЕКС (ЕЧИСЛО (- СРЕДНЕЕ (A2; СТРОКА (КОСВЕННАЯ («1:» & DLSTR (A2)))); 1)))))))) * СТРОКА (КОСВЕННАЯ («1:» & DLSTR (A2))); 0); LINE (INDIRECT («1:» & DLSTR (A2)))) + 1; 1) * 10 ^ LINE (INDIRECT («1:» & DLSTR (A2))) / 10)
Где A2 — исходная текстовая строка.
чтобы объяснить, как это работает, нужна отдельная статья. Затем вы можете просто скопировать в свою таблицу, чтобы убедиться, что она действительно работает 🙂
Обратите внимание, что в этом случае в тексте может быть несколько цифр. Все они будут извлечены и объединены в одно целое.
Однако, посмотрев на результаты, вы можете заметить небольшой недостаток: если исходный текст в ячейке не содержит числа, формула возвращает ноль, как в строке 7 на скриншоте выше. Чтобы исправить это, вы можете заключить формулу в оператор IF, который будет проверять, содержит ли исходный текст число. Если это так, формула извлекает это число, в противном случае она возвращает пустую строку:
= ЕСЛИ (СУММ (DLSTR (A2) -LSTR (ПОДСТАВИТЬ (A2; {«0»; «1»; «2»; «3»; «4»; «5»; «6»; «7»; «) 8 «;» 9 «};» «)))> 0; СУММПРОИЗВ (MID (0 & A2; LARGE (ИНДЕКС (ISNUMBER (- MID (A2; STRING (INDDIRECT (» 1: «& DLSTR (A2)))))); 1)) * LINE (INDIRECT («1:» & DLSTR (A2))); 0); LINE (INDIRECT («1:» & DLSTR (A2)))) + 1; 1) * 10 ^ СТРОКА (КОСВЕННАЯ («1:» & DLSTR (A2))) / 10);»»)
В отличие от всех предыдущих примеров, эти формулы возвращают число. Чтобы проверить это, просто посмотрите на выровненные по правому краю значения в столбце B и усеченные ведущие нули (например, 88 вместо 088).
Если число, которое вы хотите извлечь, ограничено каким-либо типом символа-разделителя, вы можете использовать функцию MID. Давайте рассмотрим пример того, как получить номер счета из текста платежа.
Мы будем искать положение символа «#» и позицию первого следующего за ним пробела. Между ними будет номер счета:
= MID (ПОДСТАВИТЬ (A2; «»; «»); НАЙТИ («N.»; ПОДСТАВИТЬ (A2; «»; «»)) + 1; НАЙТИ («»; A2; НАЙТИ («N.», A2 ; 1)) — НАЙТИ («N.»; A2; 1) -1)
На скриншоте ниже вы можете увидеть, как это работает.
Вот еще один возможный вариант удаления числа из текста. Необходимо извлечь первое встреченное число из текста.
Воспользуемся формулой
= ПОИСК (2 ^ 64; — ЛЕВЫЙ (СРЕДНИЙ (A1 & «_ 0»; МИН (НАЙТИ ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A1 & «_ 0123456789 «»)); 15); {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}))
или замените список цифр функцией:
= ПОИСК (2 ^ 64; — ЛЕВЫЙ (СРЕДНИЙ (A1 & «_ 0»; МИН (НАЙТИ ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A1 & «_ 0123456789 «»)); 15); STRING ($ A $ 1: $ IV $ 16)))
Как видите, мы получаем только первое число независимо от его позиции:
И еще один пример. Попробуем извлечь из текста все числа, разделив их своеобразным разделителем. Например, дефис “-“.
В этом случае вам нужно будет использовать формулу массива:
{= ПОДСТАВИТЬ (ОБРЕЗАТЬ (КОНТРАКТ (ЕСЛИ (ЕЧИСЛО (- MID (A2; СТРОКА (1 $: 94); 1))), MID (A2; СТРОКА (1 $: 94 $); 1);» «)));» «;»-«)}
Мы нашли все числа в тексте, как вы можете видеть на скриншоте ниже:
После исправления этой формулы вы можете использовать любой другой разделитель.
Поскольку между ними есть разделители, вы легко можете распределить эти числа по отдельным ячейкам. Как это сделать: прочитайте статью 8 способов разбить ячейку Excel на две или более.
Как выделить число из текста с помощью Ultimate Suite
Как вы только что видели, простой и понятной формулы Excel для извлечения чисел из текстовой строки не существует. Если у вас возникли проблемы с пониманием формул или их настройкой для наборов данных, вам может понравиться этот простой способ получить число из текста в Excel.
С помощью надстройки Ultimate Suite, добавленной на ленту Excel, вы можете быстро получить число из любой буквенно-цифровой строки:
- Перейдите на вкладку Данные Ablebits> Текст и нажмите Извлечь) :
- Выделите все ячейки с данными для обработки.
- На панели инструментов «Извлечь» установите переключатель «Извлечь номера)».
- В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите поле «Вставить как формулу» или оставьте его пустым (по умолчанию).
Я рекомендую активировать это поле, если вы хотите, чтобы нарисованные числа обновлялись автоматически при изменении исходных значений. Если вы хотите, чтобы результаты не зависели от будущих изменений (например, если вы планируете удалить исходные данные позже), не используйте эту опцию.
- Нажмите кнопку «Вставить результаты». Готовый!
Как и в предыдущем примере, результаты извлечения являются числами. Это означает, что вы можете подсчитывать, складывать, усреднять или производить любые другие вычисления с ними.
Если установлен флажок «Вставить как формулу», вы увидите выражение в строке формул. Вам интересно узнать, какой именно? Просто скачайте пробную версию Ultimate Suite и убедитесь сами 🙂
Если вы хотите иметь это, а также еще 60 полезных инструментов в Excel, используйте эту надстройку.
Я попытался дать вам наиболее исчерпывающий совет, как извлечь число из текста. Конечно, они не могут охватить все возможные случаи. Поэтому, если вы столкнетесь с чем-то особенно сложным, не стесняйтесь писать в комментариях. Постараюсь максимально помочь.