Генеричка формула
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Резиме
Да бисте уклонили нумеричке знакове из текстуалног низа, можете користити формулу засновану на функцији ТЕКСТЈОИН. У приказаном примеру, формула у Ц5 је:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Објашњење
Екцел нема начин да пребаци слова у текстуалном низу у низ директно у формули. Као заобилазно решење, ова формула користи функцију МИД, уз помоћ функција РЕД и ИНДИРЕКТ да би постигла исти резултат. Копирана формула у Ц5 је:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Ово изгледа прилично компликовано, али суштина је у томе да креирамо низ свих знакова у Б5 и тестирамо сваки знак да бисмо видели да ли је то број. Ако је тако, одбацујемо вредност и замењујемо је празним низом (""). Ако није, у „обрађени“ низ додамо не-нумерички знак. Коначно, користимо функцију ТЕКСТЈОИН (ново у програму Екцел 2019) за спајање свих знакова, занемарујући празне вредности.
Функционишући изнутра према унутра, МИД функција се користи за издвајање текста у Б5, један по један знак. Кључ је РОВ и ИНДИРЕЦТ фрагмент овде:
ROW(INDIRECT("1:100"))
који заврти низ који садржи 100 бројева попут овог:
(1,2,3,4,5,6,7,8… .99,100)
Напомена: 100 представља максимални број знакова за обраду. Промените се тако да одговарају вашим подацима или користите функцију ЛЕН како је објашњено у наставку.
Овај низ иде у функцију МИД као аргумент старт_нум . За нум_цхарс користимо 1.
МИД функција враћа низ попут овог:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Напомена: Додатне ставке из низа уклоњене су ради читљивости.
Овом низу додајемо нулу. Ово је једноставан трик који приморава Екцел да присили текст на број. Нумеричке вредности текста као што су „1“, „2“, „3“, „4“ итд. Претварају се без грешака, али ненумеричке вредности неће успети и избациће грешку #ВАЛУЕ. За хватање ових грешака користимо функцију ИФ са функцијом ИСЕРР. Када уочимо грешку, знамо да имамо нумерички знак, па тај знак уносимо у обрађени низ са другом МИД функцијом:
MID(B5,ROW(INDIRECT("1:100")),1)
Ако не добијете грешку, знамо да имамо број, па у поље уместо броја убацујемо празан низ ("").
Коначни резултат низа прелази у функцију ТЕКСТЈОИН као аргумент тект1. За граничник користимо празан низ (""), а за игноре_емпти наводимо ТРУЕ. Затим ТЕКСТЈОИН спаја све непразне вредности у пољу и враћа резултат.
Прецизна дужина низа
Уместо да кодирате број попут 100 у ИНДИРЕЦТ, можете да користите функцију ЛЕН за изградњу низа са стварним бројем знакова у ћелији попут ове:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
ЛЕН враћа број знакова у ћелији као број који се користи уместо 100. То омогућава да се формула аутоматски увећа до било ког броја знакова.
Уклањање додатног простора
Када уклоните нумеричке знакове, можда вам остане вишак размака. Да бисте скинули размаке на почетку и на крају и нормализирали размаке између речи, можете умотати формулу приказану на овој страници у функцију ТРИМ:
=TRIM(formula)
Са СЕКУЕНЦЕ
У програму Екцел 365, нова функција СЕКУЕНЦЕ може да замени горе наведени РЕД + ИНДИРЕКТНИ код:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Овде користимо СЕКУЕНЦЕ + ЛЕН за изградњу низа тачне дужине у једном кораку.
Са ЛЕТ
Ову формулу можемо даље усмјерити помоћу функције ЛЕТ. Будући да се низ два пута креира горе помоћу СЕКУЕНЦЕ и ЛЕН, можемо дефинирати низ као променљиву и створити га само једном:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Овде се вредност низа поставља само једном, а затим се два пута користи унутар функције МИД.
