Oleme õppinud, kuidas Excel 2016 ja vanemate versioonide lahtritelt arvväärtusi eemaldada. Meie kasutatud valemid olid pisut keerulised, kuid nüüd on mängus Excel 2019 ja 365.
Excel 2019 ja 365 tutvustavad mõningaid uusi funktsioone, mis hõlbustavad mitte -numbriliste märkide eemaldamist ja toovad uude lahtrisse ainult numbrilised väärtused. Kasutame valemeid, mis aitavad meil seda mugavamalt teha.
Üldine valem
| =TEXTJOIN("",TÕSI,IFERROR(MID(segamini_tekst,JÄRJESTUS(NumChars), 1) +0, "")) |
Segatud tekst: See on lähtetekst, millest soovite välja võtta kõik numbrilised väärtused.
NumChars: See on tähemärkide koguarv, mida soovite töödelda. Tekstis jumbled_text ei tohi olla rohkem tähemärke kui see arv (märgid ja numbrid kokku).
Vaatame asjade selgitamiseks näidet.
Näide: eemaldage mitte -numbrilised märgid ja ekstraktige kõik numbrid
Siin on meil segane tekst. See tekst sisaldab mõningaid numbreid ja mõningaid mittearvulisi märke. Mul on vaja vabaneda mitte -numbrilistest märkidest ja saada numbrilised väärtused ainult veerust D.
Ma ei oota, et segatud teksti tähemärkide koguarv oleks üle 20. Seega on NumCharsi väärtus siin 20. Vajadusel saate seda arvu suurendada.
Mitte -numbriliste märkide eemaldamiseks kasutage ülaltoodud üldvalemit.
| =TEXTJOIN("",TÕSI,IFERROR(MID(C3,JÄRJESTUS(20),1)+0,"")) |
Ja kui vajutate sisestusnuppu. Kõik numbrilised märgid eemaldatakse. Lohistage see valem alla, et eemaldada tähemärgid stringist kõigist veeru C3 lahtritest.
Kuidas see töötab?
Kõigepealt vaatame, kuidas seda valemit samm -sammult lahendatakse.
| 1-> TEXTJOIN("",TÕSI,IFERROR(MID(C3,JÄRJESTUS(20),1)+0,"")) |
| 2-> TEXTJOIN("",TÕSI,IFERROR(MID("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",TÕSI,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("",TÕSI,IFERROR({1; 2; #VALUE!;#VALUE!;#VALUE!; 1; 2; #VALUE!; 1; 2; 3; #VALUE!;#VALUE!;…; #VALUE!}+0,"")) |
| 5-> TEXTJOIN("",TÕSI,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Nagu näete, hakkab valem lahendama seestpoolt. Alguses lahendatakse funktsioon SEQUENCE. Kuna oleme läbinud 20. See tagastab numbrimassiivi vahemikus 1 kuni 20.
Seda massiivi esitatakse MID -funktsioonile stardinumbrina. Funktsioon keskel läheb igale indeksile ja jagab iga märgi. Seda näete 3. sammus.
Järgmisena lisame igale märgile 0. Kui proovite Excelis numbreid lisada mitte -numbrilistele märkidele, on tulemuseks #VALUE! Viga. Seega saame hulga numbreid ja #VALUE! Vead. Mitte numbrilised märgid on nüüd kadunud.
Järgmine funktsioon IFERROR asendab kõik #VALUE vead tähega "" (tühi). Nüüd jäävad meile numbrilised väärtused ja tühikud.
Lõpuks serveeritakse seda massiivi funktsioonile TEXTJOIN. Funktsioon TEXTJOIN ühendab need ja saame stringi, mis sisaldab ainult numbreid.
Valemi täiustamine
Ülaltoodud valemis kasutati märkide arvu töötlemiseks kõvasti kodeeritud numbrit (võtsime kakskümmend). Kuid võite soovida, et see oleks dünaamiline. Sel juhul arvasite õigesti, kasutame funktsiooni LEN. Töötlemiseks kulub täpne arv tähemärke. Nii et valem saab olema.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(segamini_tekst)),1)+0,"")) |
Siin tuvastab funktsioon LEN automaatselt tähtnumbrilise stringi täpsed numbrimärgid. See vabastab maksimaalse tähemärkide arvu määramise koormusest.
Funktsiooni SEQUENCE alternatiiv
Kui te ei soovi funktsiooni SEQUENCE kasutada, saate järjestusnumbrite genereerimiseks kasutada funktsiooni ROW ja INDIRECT.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,RIDA(KAUDSED("1:"&LEN(segamini_tekst))),1)+0,"")) |
INDIRECT teisendab teksti ("1:20") tegelikuks vahemikuks ja seejärel loetleb funktsioon ROW kõik reanumbrid vahemikus 1 kuni 20. (20 on lihtsalt näiteks. See võib olla ükskõik milline number).
Nii et jah, poisid, nii saate Excelis mitte -numbrilised tähemärgid tähestikulisest numbrist lahti rebida. Loodan, et olin piisavalt selgitav ja see artikkel aitas teid. Kui teil on selle või mõne muu exceli/VBA teemaga seoses küsimusi. Seni jätkake Excelling.
Jagage numbrid ja tekst stringist Excel 2016 ja vanemates versioonides: Kui meil ei olnud funktsiooni TEXTJOIN, kasutasime LEFT ja RIGHT funktsioone koos mõne muu funktsiooniga numbriliste ja mitte -numbriliste märkide eraldamiseks stringist.
Väljavõte Excelist stringist, kasutades Exceli vasakut ja paremat funktsiooni: Exceli teksti stringist eemaldamiseks saame kasutada Exceli funktsiooni LEFT ja RIGHT. Need funktsioonid aitavad meil stringe dünaamiliselt tükeldada.
Eemaldage Exceli tekstist esi- ja lõppruumid: Juhtivaid ja tagumisi ruume on visuaalselt raske ära tunda ja need võivad teie andmed segi ajada. Nende märkide eemaldamine stringist on andmete puhastamisel põhiline ja kõige olulisem ülesanne. Siit saate teada, kuidas seda Excelis hõlpsalt teha.
Eemaldage tähemärgid paremalt: Excelis stringi paremalt poolt märkide eemaldamiseks kasutame funktsiooni LEFT. Jah, funktsioon LEFT. Funktsioon LEFT säilitab LEFT -ist antud arvu märke ja eemaldab kõik paremalt.
Eemaldage Excelist soovimatud märgid: Soovimatute märkide eemaldamiseks Excelist stringist kasutame funktsiooni SUBSTITUTE. Funktsioon SUBSTITUTE asendab antud märgid teise antud märgiga ja loob uue muudetud stringi.
Kuidas Excelis teksti eemaldada, alustades Exceli positsioonist: Teksti eemaldamiseks stringi alguspositsioonist kasutame Exceli funktsiooni REPLACE. See funktsioon aitab meil määrata striipide lähtepositsiooni ja märkide arvu.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.