Обща формула
(=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),""))
Забележка: това е формула на масив и трябва да се въведе с 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),""))
Това изглежда доста сложно, но същността е, че ние създаваме масив от всички знаци в 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. Използваме функцията 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 новата функция SEQUENCE може да замени кода ROW + INDIRECT по-горе:
=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.
