Общая формула
(=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,"")))
Резюме
Чтобы удалить нечисловые символы из текстовой строки, вы можете попробовать эту экспериментальную формулу, основанную на функции TEXTJOIN, новой функции в Excel 2019. В показанном примере формула в C5 имеет следующий вид:
(=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,"")))
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Формула MID используется изнутри для извлечения текста в B5, по одному символу за раз. Ключ - это СТРОКА / КОСВЕННАЯ часть:
ROW(INDIRECT("1:100"))
который раскручивает массив, содержащий 100 таких чисел:
(1,2,3,4,5,6,7,8… .99,100)
Примечание: 100 представляет максимальное количество символов для обработки. Измените в соответствии с вашими данными.
Этот массив входит в функцию MID как аргумент start_num . Для num_chars мы используем 1.
Функция MID возвращает такой массив:
("1"; "0"; "0"; ""; "a"; "p"; "p"; "l"; "e"; "s"; ""; ""; ""; " «…)
(лишние элементы в массиве удалены для удобства чтения)
К этому массиву мы добавляем ноль. Это простой трюк, который заставляет Excel пытаться преобразовать текст в число. Числовые текстовые значения, такие как «1», «2», «3», «4» и т. Д., Преобразуются, в то время как нечисловые значения терпят неудачу и вызывают ошибку #VALUE. Мы используем функцию ЕСЛИОШИБКА, чтобы отловить эти ошибки и вернуть пустую строку (""), в то время как числовые значения передаются в массив. Результатом является массив, содержащий только числа и пустые строки:
(1; 0; 0; ""; ""; ""; ""; "";….)
Наконец, результат этого массива передается в функцию TEXTJOIN как аргумент text1 . В качестве разделителя мы используем пустую строку (""), а для ignore_empty мы указываем TRUE. Затем TEXTJOIN объединяет все непустые значения в массиве и возвращает результат.
Примечание. TEXTJOIN вернет числа в виде текста, например «100,« 500 »и т. Д. Если вы хотите получить истинный числовой результат, добавьте ноль или оберните всю формулу в функцию VALUE.
