Извлечь число из текстовой строки
В учебнике показано, как извлечь число из различных текстовых строк в Excel с помощью формул и инструмента «Извлечь».
Когда дело доходит до извлечения части текстовой строки заданной длины, Excel предоставляет три функции подстроки (левая, правая и средняя) для быстрого выполнения задачи. Когда дело доходит до извлечения чисел из буквенно-цифровой строки, Microsoft Excel… ничего не дает.
Чтобы получить число из строки в Excel, нужно немного смекалки, немного терпения и куча разных функций, вложенных друг в друга. Или вы можете запустить инструмент «Извлечение» и выполнить работу одним щелчком мыши. Ниже вы найдете полную информацию об обоих методах.
Как извлечь число из конца текстовой строки
Если у вас есть столбец буквенно-цифровых строк, в котором после текста стоит число, вы можете использовать следующую формулу, чтобы получить его.
ПРАВИЛЬНО(клеткаЛЕН(клетка) – МАКС(ЕСЛИ(ЧИСЛО(СРЕДНЕЕ(клеткаСТРОКА(ДВССЫЛ(“1:”&ДЛСТР(клетка))), 1) *1)=ЛОЖЬ, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(клетка))), 0)))
На логике формулы остановимся чуть позже. А пока просто замените клетка со ссылкой на ячейку, содержащую исходную строку (в нашем случае A2), и введите формулу в любую пустую ячейку в той же строке, скажем, в B2:
=ПРАВАЯ(A2, ДЛСТР(A2) – МАКС(ЕСЛИ(ЧИСЛО(ЧИСЛО(СРЕДНЯЯ(A2, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 1) *1)=ЛОЖЬ, СТРОКА(ДВССЫЛ(“1: “&ДЛСТР(A2))), 0)))
Эта формула получает число только с конца. Если в строке также есть числа в начале или в середине, они игнорируются:
Извлечение выполняется с помощью функции ПРАВО, которая относится к категории текстовых функций. Вывод этой функции всегда текст. В нашем случае результатом является числовая подстрока, которая в терминах Excel тоже является текстом, а не числом.
Если вам нужно, чтобы результатом было число (которое вы можете использовать в дальнейших вычислениях), то заверните формулу в функцию ЗНАЧ или выполните арифметическую операцию, которая не изменит результат, скажем, умножьте на 1 или прибавьте 0. Чтобы поймать ошибки в строках, не содержащих ни одного числа, используйте функцию ЕСЛИОШИБКА. Например:
=ЕСЛИОШИБКА(ЗНАЧЕНИЕ(ПРАВО(A2, ДЛСТР(A2) – МАКС(ЕСЛИ(ЕСЧИСЛО(СРЕДН(A2, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 1)*1)=ЛОЖЬ, СТРОКА(ДВССЫЛ (“1:”&ДЛСТР(A2))), 0)))), “”)
или же
=ЕСЛИОШИБКА(ПРАВО(A2, ДЛСТР(A2) – МАКС(ЕСЛИ(ЕСЧИСЛО(СРЕДН(A2, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 1) *1)=ЛОЖЬ, СТРОКА(ДВССЫЛ(” 1:”&ДЛСТР(A2))), 0))) +0, “”)
Примечание. В Dynamic Array Excel (Office 365 и 2021) вы вводите формулу обычным способом с помощью клавиши Enter. В Excel 2019 и более ранних версиях он работает только как формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы завершить его.
Как работает эта формула:
Чтобы извлечь число из буквенно-цифровой строки, первое, что вам нужно знать, это где начать извлечение. Позиция последнего нечислового символа в строке определяется с помощью этой хитрой формулы:
МАКС(ЕСЛИ(ЕСЧИСЛО(СРЕДН(A2, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 1)*1)=ЛОЖЬ, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 0) )
Чтобы понять логику, давайте исследуем его изнутри:
Комбинация ROW(INDIRECT(“1:”&LEN(A2))) создает последовательность чисел, соответствующую общему количеству символов в исходной строке (A2), и мы подаем эти последовательные числа в MID в качестве начальных чисел:
СРЕДНЕ(A2, {1;2;3;4;5;6;7;8}, 1)
Функция MID извлекает каждый отдельный символ из A2 и возвращает их в виде массива:
{“0″;”5″;”-“;”Е”;”С”;”-“;”0″;”1”}
Поскольку MID является текстовой функцией, ее вывод всегда является текстом (как вы можете заметить, все символы заключены в кавычки). Чтобы преобразовать числовые единицы в числа, мы умножаем массив на 1 (двойное отрицание –MID() будет иметь тот же эффект). Результатом этой операции является массив чисел и #ЗНАЧ! ошибки, представляющие нечисловые символы:
IЧИСЛО({0;5;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;0;1})
Функция ISNUMBER оценивает каждый элемент массива и выдает свой вердикт в виде логических значений — TRUE для чисел, FALSE для всего остального:
{ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА}
Этот массив проходит логическую проверку функции ЕСЛИ, где каждый элемент массива сравнивается с ЛОЖЬЮ:
ЕСЛИ({ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА}=ЛОЖЬ, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 0)
Для каждого FALSE (нечислового значения) другая функция ROW(INDIRECT()) возвращает его относительную позицию в строке. Для каждого TRUE (числовое значение) возвращается ноль. Результирующий массив выглядит следующим образом:
{0;0;3;4;5;6;0;0}
Остальное легко. Функция MAX находит наибольшее число в приведенном выше массиве, которое является позицией последнего нечислового значения в строке (6 в нашем случае). Просто вычтите эту позицию из общей длины строки, возвращаемой LEN, и передайте результат в RIGHT, чтобы он знал, сколько символов нужно извлечь из правой части строки:
ВПРАВО(A2, ДЛИН.(A2) – 6)
Сделанный!
Как извлечь число из начала текстовой строки
Если вы работаете с записями, в которых текст стоит после числа, вы можете извлечь число из начала строки, используя эту общую формулу:
ОСТАВИЛ(клеткаСОВПАДЕНИЕ(ЛОЖЬ, IНОМЕР(СРЕДНИЙ(клеткаСТРОКА(ДВССЫЛ(“1:”&ДЛСТР(клетка)+1)), 1) *1), 0) -1)
С исходной строкой в A2 используйте следующую формулу, чтобы получить число:
=ЛЕВО(A2, СОВПАДЕНИЕ(ЛОЖЬ, IНОМЕР(СРЕДН(A2, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2)+1)), 1) *1), 0) -1)
Независимо от того, сколько цифр в середине или в конце, извлекается только начальное число:
Примечание. В Excel 365 и Excel 2021 благодаря поддержке динамических массивов обычная формула работает нормально. В Excel 2019 и более ранних версиях вы должны нажать Ctrl + Shift + Enter, чтобы явно сделать его формулой массива.
Как работает эта формула:
Здесь мы снова используем комбинацию функций СТРОКА, ДВССЫЛ и ДЛСТР, чтобы создать последовательность чисел, равную общему количеству символов в исходной строке плюс 1 (роль этого дополнительного символа станет ясна чуть позже).
СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2)+1))
MID и ISNUMBER выполняют ту же работу, что и в предыдущем примере — MID извлекает отдельные символы, а ISNUMBER преобразует их в логические значения. Результирующий массив значений ИСТИНА и ЛОЖЬ поступает в функцию ПОИСКПОЗ в виде массива поиска:
СОВПАДЕНИЕ (ЛОЖЬ, {ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ}, 0)
ПОИСКПОЗ вычисляет относительную позицию первого FALSE, давая нам позицию первого нечислового символа в строке (3 в A2). Чтобы извлечь предыдущие числа, мы вычитаем 1 из позиции первого текстового символа и передаем разницу в num_chars аргумент ЛЕВОЙ функции:
ВЛЕВО(A2, 3-1)
Теперь вернемся к «лишнему» символу в последовательности, сгенерированной ROW(INDIRECT()+1)). Как вы уже знаете, эта последовательность служит отправной точкой для функции MID. Без +1 MID извлечет ровно столько символов, сколько содержится в исходной строке. Если строка содержит только числа, ISNUMBER вернет только TRUE, а MATCH требует хотя бы одного FALSE. Для этого мы добавляем к общей длине строки еще один символ, который функция MID преобразует в пустую строку. Например, в B7 MID возвращает этот массив:
{“1″;”2″;”3″;”4″;””}
Примечание. Как и в случае с функцией RIGHT, LEFT также возвращает числовую подстроку, которая технически является текстом, а не числом. Чтобы получить результат в виде числа, а не числовой строки, вложите формулу в функцию ЗНАЧ или умножьте результат на 1, как показано в первом примере.
Как получить число с любой позиции в строке
Если ваша задача заключается в извлечении числа из любого места строки, вы можете воспользоваться следующей умопомрачительной формулой, опубликованной на MrExcel форум:
=СУММПРОИЗВ(СРЕДН(0&A2, НАИБОЛЬШИЙ(ИНДЕКС(ЧИСЛО(–СРЕДН(A2, СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))), 1)) * СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2) )), 0), СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2))))+1, 1) * 10^СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(A2)))/10)
Где A2 — исходная текстовая строка.
Разбор этой формулы потребует отдельной статьи, поэтому вы можете просто скопировать ее на свой рабочий лист, чтобы убедиться, что она действительно работает 🙂
Однако при изучении результатов можно заметить один незначительный недостаток — если исходная строка не содержит числа, формула возвращает ноль, как в строке 6 на скриншоте выше. Чтобы исправить это, вы можете обернуть формулу в оператор IF, логическая проверка которого проверяет, содержит ли исходная строка какое-либо число. Если это так, формула извлекает число, иначе возвращает пустую строку:
=ЕСЛИ(СУММ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,” 8″,”9″}, “”)))>0, СУММПРОИЗВ(СРЕДН(0&A2, НАИБОЛЬШИЙ(ИНДЕКС(ЧИСЛО(–СРЕДН(A2,СТРОКА(ДВССЫЛ(“$1:$”&ДЛСТР(A2))), 1))* СТРОКА(ДВССЫЛ(“$1:$”&ДЛСТР(A2))),0), СТРОКА(ДВССЫЛ(“$1:$”&ДЛС(A2))))+1,1) * 10^СТРОКА(ДВССЫЛ (“$1:$”&ДЛСТР(A2)))/10),””)
Как показано на снимке экрана ниже, улучшенная формула прекрасно работает (спасибо Алексу, нашему гуру Excel, за это улучшение):
В отличие от всех предыдущих примеров результатом этой формулы является число. Чтобы убедиться в этом, просто обратите внимание на выровненные по правому краю значения в столбце B и усеченные ведущие нули.
Кончик. В Excel 365 – Excel 2019 есть гораздо более простое решение с помощью функции TEXTJOIN. См. Как удалить текст и сохранить числа.
Извлечь число из текстовой строки с помощью Ultimate Suite
Как вы только что видели, не существует тривиальной формулы Excel для извлечения числа из текстовой строки. Если у вас есть трудности с пониманием формул или их настройкой для ваших наборов данных, вам может понравиться этот простой способ получить число из строки в Excel.
С нашим Ultimate Suite, добавленным на вашу ленту Excel, вы можете быстро получить число из любой буквенно-цифровой строки:
- Перейти к Данные об аблебитах вкладка > Текст группу и нажмите Извлечь:
- Выберите все ячейки с исходными строками.
- На панели инструмента «Извлечь» выберите переключатель «Извлечь числа».
- В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, установите флажок «Вставить как формулу» или оставьте его невыбранным (по умолчанию).
Я советую установить этот флажок, если вы хотите, чтобы извлеченные числа автоматически обновлялись, как только в исходные строки вносятся какие-либо изменения. Если вы хотите, чтобы результаты не зависели от исходных строк (например, если вы планируете удалить исходные данные позже), не устанавливайте этот флажок.
- Нажмите кнопку Вставить результаты. Сделанный!
Как и в предыдущем примере, результаты извлечения являются числами, то есть вы можете считать, суммировать, усреднять или выполнять любые другие вычисления с ними.
В этом примере мы решили вставить результаты в виде значений, и надстройка сделала именно то, о чем просили:
Если Вставить как формулу Если флажок установлен, вы увидите формулу в строке формул. Интересно узнать какой? Просто скачайте пробную версию Ultimate Suite и убедитесь сами 🙂
Доступные загрузки
Номер извлечения Excel — образец книги (файл .xlsx)
Ultimate Suite – пробная версия (файл .exe)
