Загальна формула
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Резюме
Щоб видалити числові символи з текстового рядка, ви можете використовувати формулу на основі функції TEXTJOIN. У наведеному прикладі формула в С5 має вигляд:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + 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),""))
Це виглядає досить складно, але суть полягає в тому, що ми створюємо масив усіх символів у В5 і тестуємо кожен символ, щоб перевірити, чи це число. Якщо так, ми відкидаємо значення і замінюємо його порожнім рядком (""). Якщо ні, ми додаємо нечисловий символ до "обробленого" масиву. Нарешті, ми використовуємо функцію 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. Ми використовуємо функцію IF з функцією 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.
