Generička formula
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Sažetak
Da biste uklonili numeričke znakove iz tekstualnog niza, možete upotrijebiti formulu koja se temelji na funkciji TEXTJOIN. U prikazanom primjeru formula u C5 je:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Obrazloženje
Excel nema način za prebacivanje slova u tekstualnom nizu u niz izravno u formuli. Kao zaobilazno rješenje, ova formula koristi funkciju MID, uz pomoć funkcija ROW i INDIRECT kako bi postigla isti rezultat. Kopirana formula u C5 je:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Ovo izgleda prilično komplicirano, ali suština je u tome da u B5 stvorimo niz svih znakova i testiramo svaki znak da vidimo je li to broj. Ako je tako, odbacujemo vrijednost i zamjenjujemo je praznim nizom (""). Ako nije, dodamo nenumerički znak u "obrađeni" niz. Konačno, koristimo funkciju TEXTJOIN (novo u programu Excel 2019) za spajanje svih znakova, zanemarujući prazne vrijednosti.
Radeći iznutra prema van, funkcija MID koristi se za izdvajanje teksta u B5, jedan po jedan znak. Ključ je ROW i INDIRECT isječak ovdje:
ROW(INDIRECT("1:100"))
koji zavrti niz koji sadrži 100 ovakvih brojeva:
(1,2,3,4,5,6,7,8… .99.100)
Napomena: 100 predstavlja maksimalni broj znakova za obradu. Promijenite u skladu s podacima ili upotrijebite funkciju LEN kako je objašnjeno u nastavku.
Ovaj niz ulazi u funkciju MID kao argument start_num . Za num_chars koristimo 1.
Funkcija MID vraća niz poput ovog:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Napomena: Dodatne stavke u polju uklonjene su zbog čitljivosti.
Ovom polju dodamo nulu. Ovo je jednostavan trik koji prisiljava Excel da prisili tekst na broj. Numeričke tekstualne vrijednosti poput "1", "2", "3", "4" itd. Pretvaraju se bez pogrešaka, ali nenumeričke vrijednosti neće uspjeti i pojavit će se pogreška #VALUE. Za hvatanje ovih pogrešaka koristimo funkciju IF s funkcijom ISERR. Kad uočimo pogrešku, znamo da imamo numerički znak, pa taj znak unosimo u obrađeni niz s drugom MID funkcijom:
MID(B5,ROW(INDIRECT("1:100")),1)
Ako ne dobijete pogrešku, znamo da imamo broj, pa u polje umjesto broja umetćemo prazan niz ("").
Konačni rezultat polja ide u funkciju TEXTJOIN kao argument text1. Za graničnik koristimo prazan niz (""), a za ignore_empty navodimo TRUE. TEXTJOIN zatim spaja sve neprazne vrijednosti u polju i vraća rezultat.
Precizna duljina niza
Umjesto da kodirate broj poput 100 u INDIRECT, možete koristiti funkciju LEN za izgradnju niza sa stvarnim brojem znakova u ćeliji poput ove:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN vraća broj znakova u ćeliji kao broj koji se koristi umjesto 100. To omogućuje da se formula automatski poveća na bilo koji broj znakova.
Uklanjanje dodatnog prostora
Kad uklonite numeričke znakove, možda će vam ostati višak razmaka. Da biste uklonili vodeće i prateće razmake i normalizirali razmake između riječi, možete omotati formulu prikazanu na ovoj stranici unutar funkcije TRIM:
=TRIM(formula)
S SEKVENCOM
U programu Excel 365, nova funkcija SEQUENCE može zamijeniti gore navedeni RED + INDIREKTNI kôd:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Ovdje koristimo SEQUENCE + LEN kako bismo u jednom koraku izgradili niz ispravne duljine.
Uz LET
Ovu formulu možemo dalje usmjeriti pomoću funkcije LET. Budući da je niz dva puta stvoren gore pomoću SEQUENCE i LEN, možemo definirati niz kao varijablu i stvoriti ga samo jednom:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Ovdje se vrijednost polja postavlja samo jednom, a zatim se dva puta koristi unutar funkcije MID.
