Общая формула
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Резюме
Чтобы удалить числовые символы из текстовой строки, вы можете использовать формулу, основанную на функции TEXTJOIN. В показанном примере формула C5 имеет следующий вид:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Объяснение
В Excel нет способа преобразовать буквы текстовой строки в массив непосредственно в формуле. В качестве обходного пути в этой формуле используется функция MID с помощью функций ROW и INDIRECT для достижения того же результата. Скопированная формула в C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Это выглядит довольно сложно, но суть в том, что мы создаем массив всех символов в B5 и проверяем каждый символ, чтобы увидеть, является ли он числом. Если это так, мы отбрасываем значение и заменяем его пустой строкой (""). Если нет, мы добавляем нечисловой символ в «обработанный» массив. Наконец, мы используем функцию TEXTJOIN (новая в Excel 2019) для объединения всех символов вместе, игнорируя пустые значения.
Работая изнутри, функция MID используется для извлечения текста в B5, по одному символу за раз. Ключевым моментом здесь является фрагмент ROW и INDIRECT:
ROW(INDIRECT("1:100"))
который раскручивает массив, содержащий 100 таких чисел:
(1,2,3,4,5,6,7,8… .99,100)
Примечание: 100 представляет максимальное количество символов для обработки. Измените в соответствии с вашими данными или используйте функцию LEN, как описано ниже.
Этот массив входит в функцию MID как аргумент start_num . Для num_chars мы используем 1.
Функция MID возвращает такой массив:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Примечание: лишние элементы в массиве удалены для удобства чтения.
К этому массиву мы добавляем ноль. Это простой трюк, который заставляет Excel преобразовывать текст в число. Числовые текстовые значения, такие как «1», «2», «3», «4» и т. Д., Преобразуются без ошибок, но нечисловые значения завершатся ошибкой и вызовут ошибку #VALUE. Мы используем функцию ЕСЛИ с функцией ISERR, чтобы отловить эти ошибки. Когда мы видим ошибку, мы знаем, что у нас есть нечисловой символ, поэтому мы переносим этот символ в обработанный массив с помощью другой функции MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Если ошибки не возникает, значит, мы знаем, что у нас есть номер, поэтому вставляем в массив пустую строку ("") вместо числа.
Окончательный результат массива передается в функцию TEXTJOIN в качестве аргумента text1. В качестве разделителя мы используем пустую строку (""), а для ignore_empty мы указываем TRUE. Затем TEXTJOIN объединяет все непустые значения в массиве и возвращает результат.
Точная длина массива
Вместо того, чтобы жестко кодировать число, такое как 100, в INDIRECT, вы можете использовать функцию LEN для создания массива с фактическим количеством символов в ячейке следующим образом:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN возвращает количество символов в ячейке в виде числа, которое используется вместо 100. Это позволяет формуле автоматически масштабироваться до любого количества символов.
Удаление лишнего места
Когда вы удаляете числовые символы, у вас могут остаться лишние пробелы. Чтобы удалить начальные и конечные пробелы и нормализовать пробелы между словами, вы можете заключить формулу, показанную на этой странице, в функцию TRIM:
=TRIM(formula)
С ПОСЛЕДОВАТЕЛЬНОСТЬЮ
В Excel 365 новая функция ПОСЛЕДОВАТЕЛЬНОСТЬ может заменить приведенный выше код СТРОКА + ДВССЫЛКА:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Здесь мы используем SEQUENCE + LEN, чтобы построить массив правильной длины за один шаг.
С LET
Мы можем дополнительно оптимизировать эту формулу с помощью функции LET. Поскольку массив создается дважды с помощью SEQUENCE и LEN, мы можем определить массив как переменную и создать его только один раз:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Здесь значение массива устанавливается только один раз, а затем используется дважды внутри функции MID.
