Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako ukloniti brojeve iz teksta u ćeliji u Excelu i Google tablicama.
Razgovarat ćemo o dvije različite formule za uklanjanje brojeva iz teksta u Excelu.
ZAMJENA Funkcijska formula
Možemo koristiti formulu koja se temelji na funkciji ZAMJENA. To je duga formula, ali jedan je od najjednostavnijih načina za uklanjanje brojeva iz alfanumeričkog niza.
U ovoj smo formuli ugnijezdili funkcije SUBSTITUTE 10 puta, ovako:
| 1 | = ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA (ZAMJENA "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Formula TEXTJOIN niza
Za uklanjanje brojeva iz alfanumeričkih nizova također možemo koristiti složenu formulu niza koja se sastoji od funkcija TEXTJOIN, MID, ROW i INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Napomena: TEXTJOIN je nova Excel funkcija dostupna u Excel 2022+ i Office 365.
Ovo je složena formula pa ćemo je podijeliti u korake da bismo je bolje razumjeli.
Korak 1
MID funkcija koristi se za izdvajanje alfanumeričkog niza na temelju argumenata start_num i num_chars.
Za argument start_num u funkciji MID koristit ćemo rezultirajući popis niza iz funkcija ROW i INDIRECT.
| 1 | = ROW (INDIREKTNO ("1:" & LEN (B3))) |
A za argument num-chars stavit ćemo 1. Nakon stavljanja argumenata u funkciju MID, vratit će niz.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
Korak 2
Dodati ćemo nulu svakoj vrijednosti u rezultirajućem nizu (koji dobivamo iz gornje MID funkcije). U Excelu, ako dodate brojeve u numeričke znakove, dobit ćemo #VREDNOST! Pogreška. Dakle, nakon dodavanja 0 u gornji niz, dobit ćemo niz brojeva i #Vrijednost! Pogreške.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0} |
Korak 3
Nakon dodavanja 0, rezultirajući niz se stavlja u funkciju ISERR. Kao što znamo da funkcija ISERR vraća TRUE za pogreške i FALSE za vrijednosti bez pogreške.
Dakle, dat će niz TRUE i FALSE, TRUE za ne-numeričke znakove i FALSE za brojeve.
| 1 | = ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0) |
Korak 4
Sada ćemo dodati IF funkciju.
IF funkcija će provjeriti rezultat ISERR funkcije (korak 3). Ako je njegova vrijednost TRUE, vratit će niz svih znakova alfanumeričkog niza. Za to smo dodali još jednu MID funkciju bez dodavanja nule na kraju. Ako je vrijednost IF funkcije FALSE, vratit će prazno (“”).
Na ovaj način imat ćemo niz koji sadrži samo ne-numeričke znakove niza.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
Korak 5
Konačno, gornji niz se stavlja u funkciju TEXTJOIN. Funkcija TEXTJOIN pridružit će sve znakove gornjeg niza i zanemariti prazan niz.
Razdjelnik za ovu funkciju postavlja se prazan niz (“”), a vrijednost argumenta ignore_empty unosi se u TRUE.
To će nam dati željeni rezultat, tj. Samo ne-numeričke znakove alfanumeričkog niza.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Napomena: Ovo je formula niza. Prilikom unosa formula niza u Excel 2022 ili starije morate koristiti CTRL + SHIFT + ENTER za unos formule umjesto regularne UNESI.
Znat ćete da ste ispravno unijeli formulu prema pojavljenim zagradama. NEMOJTE ručno unositi kovrčave zagrade, formula neće raditi.
Uz Office 365 (i vjerojatno verzije Excela nakon 2022.) formulu možete jednostavno unijeti kao i obično.
TRIM funkcija
Kad se brojevi uklone iz niza, možda će nam ostati dodatni razmaci. Da bismo uklonili sve prazne i vodeće razmake, te dodatne razmake između riječi, možemo upotrijebiti funkciju TRIM prije glavne formule, ovako:
| 1 | = TRIM (C3) |
Uklonite brojeve iz teksta u Google tablicama
Formula za uklanjanje brojeva iz teksta radi potpuno isto u Google tablicama kao i u Excelu:
