Как быстро извлечь число из текста в 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 — исходная текстовая строка.
чтобы объяснить, как это работает, нужна отдельная статья. Затем вы можете просто скопировать в свою таблицу, чтобы убедиться, что она действительно работает
