Laadige alla töövihiku näide
See õpetus näitab, kuidas Exceli ja Google'i arvutustabelite lahtris olevast tekstist numbreid eemaldada.
Arutame kahte erinevat valemit numbrite eemaldamiseks tekstist Excelis.
FUNKTSIOONI VAHEND
Võime kasutada valemit, mis põhineb funktsioonil SUBSTITUTE. See on pikk valem, kuid see on üks lihtsamaid viise numbrite eemaldamiseks tähtnumbrilisest stringist.
Selles valemis oleme asendusfunktsioone 10 korda pesastanud, näiteks:
| 1 | = ASENDUS (ASENDAJA (ASENDAJA (ASENDAJA (ASENDAJA (ASENDAJA (ASENDAJA (ASENDAJA (ASENDAJA (ASENDAJA (B3,1, "")), 2, ""), 3, ""), 4, ""), "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Massiivi TEXTJOIN valem
Numbrite eemaldamiseks tähtnumbrilistest stringidest võime kasutada ka keerulist massiivivalemit, mis koosneb funktsioonidest TEXTJOIN, MID, ROW ja INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Märkus. TEXTJOIN on uus Exceli funktsioon, mis on saadaval rakendustes Excel 2022+ ja Office 365.
See on keeruline valem, nii et jagame selle etappideks, et seda paremini mõista.
Samm 1
Funktsiooni MID kasutatakse tähtnumbriliste stringide eraldamiseks argumentide start_num ja num_chars alusel.
Funktsiooni MID argumendi start_num jaoks kasutame tulemuste massiivi loendit funktsioonidest ROW ja INDIRECT.
| 1 | = RIDA (KAUDNE ("1:" & LEN (B3))) |
Ja argumendi num-chars jaoks paneme 1. Pärast argumentide funktsiooni MID lisamist tagastab see massiivi.
| 1 | {= KESKMINE (B3, RIDA (KAUDSE ("1:" & LEN (B3))), 1)} |
2. samm
Lisame tulemuseks oleva massiivi igale väärtusele nulli (mille saame ülaltoodud MID -funktsioonist). Kui lisate Excelis numbrid mitte-numbrilistele märkidele, saame #VALUE! Viga. Niisiis, pärast 0 lisamist ülaltoodud massiivi saame numbrite massiivi ja #Väärtus! Vead.
| 1 | {= KESKMINE (B3, RIDA (KAUDSE ("1:" & LEN (B3)))), 1) +0} |
3. samm
Pärast 0 lisamist sisestatakse saadud massiiv ISERR -funktsiooni. Nagu me teame, tagastab funktsioon ISERR vigade korral tõese väärtuse ja veaväliste väärtuste puhul vale.
Seega annab see massiivi TRUE ja FALSE, TRUE mitte-numbriliste märkide jaoks ja FALSE numbrite jaoks.
| 1 | = ISERR (MID (B3, RIDA (KAUDSE ("1:" & LEN (B3)))), 1) +0) |
4. samm
Nüüd lisame funktsiooni IF.
Funktsioon IF kontrollib ISERR -funktsiooni tulemust (3. samm). Kui selle väärtus on TRUE, tagastab see massiivi, mis sisaldab kõiki tähtnumbrilise stringi märke. Selleks oleme lisanud veel ühe MID -funktsiooni, lisamata lõppu nulli. Kui funktsiooni IF väärtus on VÄÄR, tagastab see tühja (“”).
Sel viisil on meil massiiv, mis sisaldab ainult stringi mitte-numbrilisi märke.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
5. samm
Lõpuks pannakse ülaltoodud massiiv funktsiooni TEXTJOIN. Funktsioon TEXTJOIN ühendab kõik ülaltoodud massiivi märgid ja ignoreerib tühja stringi.
Selle funktsiooni eraldajaks määratakse tühi string (“”) ja argumendi ignore_empty väärtus sisestatakse TRUE.
See annab meile soovitud tulemuse, st ainult tähtnumbrilise stringi mitte-numbrilised märgid.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Märkus. See on massiivivalem. Massiivivalemite sisestamisel Excelis 2022 või varasemas versioonis peate kasutama CTRL + SHIFT + ENTER tavalise asemel valemi sisestamiseks SISENEMA.
Ilmuvate lokkisulgude järgi teate, et sisestasite valemi õigesti. ÄRGE sisestage lokkisulgi käsitsi, valem ei tööta.
Office 365 (ja eeldatavasti Exceli versioonid pärast 2022. aastat) abil saate lihtsalt sisestada valemi nagu tavaliselt.
TRIM funktsioon
Kui numbrid stringist eemaldatakse, võib meil olla täiendavaid tühikuid. Kõigi lõpp- ja eesmiste tühikute ning sõnadevaheliste lisavahede eemaldamiseks võime kasutada funktsiooni TRIM enne põhivalemit järgmiselt:
| 1 | = TRIM (C3) |
Eemaldage numbrid Google'i arvutustabelite tekstist
Numbrite tekstist eemaldamise valem töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
