Загрузите образец книги
Это руководство будет продемонстрируют вам, как найти и извлечь число из текстовой строки в Excel и Google Таблицах.
Найти и извлечь число из строки
Иногда ваши данные могут содержать числа и текст, и вы хотите извлечь числовые данные.
Если числовая часть находится справа или слева от строки, разделить число и текст относительно легко. Однако, если числа находятся внутри строки, то есть между двумя текстовыми строками, вам нужно будет использовать гораздо более сложную формулу (показанную ниже).
TEXTJOIN - Извлечение чисел в Excel 2016+
В Excel 2016 была представлена функция TEXTJOIN, которая может извлекать числа из любого места текстовой строки. Вот формула:
| 1 | = TEXTJOIN (""; ИСТИНА; ЕСЛИОШИБКА ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))); 1) * 1), "")) |
Посмотрим, как работает эта формула.
Функции ROW, INDIRECT и LEN возвращают массив чисел, соответствующих каждому символу в вашей буквенно-цифровой строке. В нашем случае «Monday01Day» состоит из 11 символов, поэтому функция ROW вернет массив {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", ИСТИНА, ЕСЛИОШИБКА ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1) * 1), "")) |
Затем функция MID извлекает каждый символ из текстовой строки, создавая массив, содержащий исходную строку:
| 1 | = TEXTJOIN ("", ИСТИНА, ЕСЛИОШИБКА (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "} * 1)," ")) |
Умножение каждого значения в массиве на 1 создаст массив, содержащий ошибки. Если символом массива был текст:
| 1 | = TEXTJOIN ("", ИСТИНА, ЕСЛИ ОШИБКА (({# ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; 0; 1; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ !}), "")) |
Затем функция ЕСЛИОШИБКА удаляет значения ошибок:
| 1 | = TEXTJOIN ("", ИСТИНА, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Оставляем только функцию TEXTJOIN, которая объединяет оставшиеся числа.
Обратите внимание, что формула выдаст вам все числовые символы вместе из вашей строки. Например, если ваша буквенно-цифровая строка - Monday01Day01, в результате вы получите 0101.
Извлечение чисел - до Excel 2016
До Excel 2016 вы могли использовать гораздо более сложный метод извлечения чисел из текста. Вы можете использовать функцию НАЙТИ вместе с функциями ЕСЛИОШИБКА и МИН, чтобы определить как первую позицию числовой части, так и первую позицию текстовой части после числа. Затем мы просто извлекаем числовую часть с помощью функции MID.
| 1 | = MID (B3; MIN (ЕСЛИОШИБКА (НАЙТИ ({0,1,2,3,4,5,6,7,8,9}; B3), 999999999)), MIN (ЕСЛИОШИБКА (ПОИСК ({"a" , «b», «c», «d», «e», «f», «g», «h», «I», «j», «k», «l», «m», « n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (ЕСЛИОШИБКА (НАЙТИ ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999)) - МИН (ЕСЛИОШИБКА (НАЙТИ ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Примечание: это формула массива, вы должны ввести формулу, нажав CTRL + SHIFT + ENTER, а не просто ENTER.
Давайте посмотрим, как работает эта формула шаг за шагом.
Найти номер кулака
Мы можем использовать функцию НАЙТИ, чтобы найти начальную позицию числа.
| 1 | = МИН (ЕСЛИОШИБКА (НАЙТИ ({1,2,3,4,5,6,7,8,9,0}; B3); 999999999)) |
В качестве аргумента find_text функции FIND мы используем константу массива {0,1,2,3,4,5,6,7,8,9}, что заставляет функцию FIND выполнять отдельный поиск для каждого значения в константа массива.
Аргумент внутри_текста функции НАЙТИ в нашем случае - Monday01Day, в котором 1 можно найти в позиции 8 и 0 в позиции 7, поэтому наш массив результатов будет: {8, # VALUE, # VALUE, # VALUE, # ЗНАЧЕНИЕ, # ЗНАЧЕНИЕ, # ЗНАЧЕНИЕ, # ЗНАЧЕНИЕ, # ЗНАЧЕНИЕ, 7}.
Используя функцию ЕСЛИОШИБКА, мы заменяем ошибки #VALUE на 999999999. Затем мы просто ищем минимум в этом массиве и, следовательно, получаем место первого числа (7).
Обратите внимание, что приведенная выше формула является формулой массива, вам нужно нажать Ctrl + Shift + Enter, чтобы вызвать ее.
Найти первый текстовый символ после числа
Подобно поиску первого числа в строке, мы используем функцию НАЙТИ, чтобы определить, где текст снова начинается после числа, хорошо используя также аргумент функции start_num.
| 1 | = MIN (ЕСЛИОШИБКА (НАЙТИ ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Эта формула работает очень похоже на предыдущую, используемую для поиска первого числа, только мы используем буквы в нашей константе массива, и поэтому числа вызывают ошибки #VALUE. Обратите внимание, что мы начинаем поиск только после позиции, определенной для первого числа (это будет аргумент start_num), а не с начала строки.
Не забудьте нажать Ctrl + Shift + Enter, чтобы воспользоваться приведенной выше формулой.
Не остается ничего, кроме как сложить все это вместе.
Извлечь номер из двух текстов
Когда у нас есть начальная позиция числовой части и начало текстовой части после этого, мы просто используем функцию MID для извлечения желаемой числовой части.
| 1 | = MID (B3; C3; D3-C3) |
Другой метод
Не объясняя это более подробно, вы также можете использовать приведенную ниже сложную формулу, чтобы получить число внутри строки.
| 1 | = СУММПРОИЗВ (СРЕДНЯЯ (0 & B3; НАИБОЛЬШИЙ (ИНДЕКС (- СРЕДНЕЕ (B3; СТРОКА (КОСВЕННАЯ ("1:" & ДЛИННОЕ (B3))); 1)) * СТРОКА (КОСВЕННАЯ ("1:" & ДЛИН (B3) )), 0), СТРОКА (КОСВЕННАЯ ("1:" & LEN (B3)))) + 1,1) * 10 ROW (INDIRECT ("1:" & LEN (B3))) / 10) |
Имейте в виду, что приведенная выше формула даст вам 0, если в строке нет числа, а ведущие нули будут опущены.
Найти и извлечь число из строки в текст в Google Таблицах
Приведенные выше примеры работают в Google Таблицах так же, как и в Excel.
