Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako odstraniti številke iz besedila v celici v Excelu in Google Preglednicah.
Razpravljali bomo o dveh različnih formulah za odstranjevanje številk iz besedila v Excelu.
ZAMENJA Funkcijska formula
Lahko uporabimo formulo, ki temelji na funkciji SUBSTITUTE. To je dolga formula, vendar je eden najlažjih načinov za odstranitev številk iz alfanumeričnega niza.
V tej formuli imamo 10 -krat ugnezdene funkcije SUBSTITUTE, na primer:
| 1 | = ZAMENJALNIK (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE) "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Formula matrike TEXTJOIN
Če želite odstraniti številke iz alfanumeričnih nizov, lahko uporabimo tudi kompleksno formulo matrike, ki jo sestavljajo funkcije TEXTJOIN, MID, ROW in INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Opomba: TEXTJOIN je nova Excelova funkcija, ki je na voljo v Excelu 2022+ in Office 365.
To je zapletena formula, zato jo bomo razdelili na korake, da jo bolje razumemo.
Korak 1
Funkcija MID se uporablja za ekstrahiranje alfanumeričnega niza na podlagi argumentov start_num in num_chars.
Za argument start_num v funkciji MID bomo uporabili rezultat matrike iz funkcij ROW in INDIRECT.
| 1 | = ROW (INDIRECT ("1:" & LEN (B3))) |
Za argument num-chars bomo dali 1. Ko argumente vnesete v funkcijo MID, bo vrnila matriko.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
2. korak
Vsaki vrednosti v dobljeni matriki (ki jo dobimo iz zgornje funkcije MID) bomo dodali nič. Če v Excelu dodate številke neštevilčnim znakom, dobimo #VALUE! Napaka. Po dodajanju 0 v zgornjo matriko bomo dobili niz številk in #Vrednost! Napake.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0} |
3. korak
Po dodajanju 0 je nastalo polje postavljeno v funkcijo ISERR. Kot vemo, funkcija ISERR vrne TRUE za napake in FALSE za vrednosti brez napak.
Tako bo podala niz TRUE in FALSE, TRUE za neštevilske znake in FALSE za številke.
| 1 | = ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0) |
4. korak
Zdaj bomo dodali funkcijo IF.
Funkcija IF bo preverila rezultat funkcije ISERR (korak 3). Če je njegova vrednost TRUE, bo vrnila niz vseh znakov alfanumeričnega niza. Za to smo dodali še eno funkcijo MID brez dodajanja ničle na koncu. Če je vrednost funkcije IF FALSE, bo vrnila prazno (“”).
Na ta način bomo imeli matriko, ki vsebuje samo neštevilčne znake niza.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
5. korak
Nazadnje je zgornja matrika postavljena v funkcijo TEXTJOIN. Funkcija TEXTJOIN se bo pridružila vsem znakom zgornje matrike in prezrla prazen niz.
Ločilo za to funkcijo je nastavljeno na prazen niz (“”) in vrednost argumenta ignore_empty je vnesena v TRUE.
Tako dobimo želeni rezultat, torej samo neštevilčne znake alfanumeričnega niza.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Opomba: To je formula matrike. Pri vnosu formul matrike v Excel 2022 ali starejše morate uporabiti CTRL + SHIFT + ENTER namesto običajnega vnesite formulo ENTER.
Po kodrastih oklepajih, ki se prikažejo, boste vedeli, da ste pravilno vnesli formulo. NI ročno vtipkajte kodrastih oklepajev, formula ne bo delovala.
S storitvijo Office 365 (in predvidoma različicami Excela po letu 2022) lahko preprosto vnesete formulo kot običajno.
TRIM funkcija
Ko številke odstranimo iz niza, bomo morda imeli dodatne presledke. Če želite odstraniti vse zadnje in vodilne presledke ter dodatne presledke med besedami, lahko pred glavno formulo uporabimo funkcijo TRIM, na primer:
| 1 | = TRIM (C3) |
Odstranite številke iz besedila v Google Preglednicah
Formula za odstranitev številk iz besedila deluje v Google Preglednicah popolnoma enako kot v Excelu:
