Oleme õppinud, kuidas Excel 2016 ja vanemate versioonide lahtritelt arvväärtusi eemaldada. Meie kasutatud valemid olid natuke keerulised, kuid nüüd on Excel 2019 ja 365 mängus uute mänguasjadega, ma mõtlen funktsioone.
Excel 2019 ja 365 tutvustavad mõningaid uusi funktsioone (TEXTJOIN ja SEQUENCE), mis võivad hõlbustada numbriliste märkide eemaldamist ja uude lahtrisse ainult numbriliste väärtuste toomist. Kasutame valemeid, mis aitavad meil seda mugavamalt teha.
Üldine valem
| =TEXTJOIN("",TÕSI,KUI(ISERROR(MID(segamini_tekst,JÄRJESTUS(NumChars), 1) +0),MID(segamini_tekst,JÄRJESTUS(NumChars), 1), ""))) |
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 numbrimärgid ja eraldage kõik tähestikud ja mitte -numbrilised märgid.
Nii et siin on meil mõni tähtnumbriline tekst. See tekst sisaldab mõningaid numbreid ja mõningaid mittearvulisi märke. Mul on vaja vabaneda numbrimärkidest ja hankida D -veerust ainult tähestikud ja muud märgiväärtused.
Ma ei oota, et segatud teksti tähemärkide arv ületab 100. Seega on NumCharsi väärtus siin 100. Vajadusel saate seda arvu suurendada või vähendada.
Numbrimärkide eemaldamiseks kasutage ülaltoodud üldvalemit.
| =TEXTJOIN("",TÕSI,KUI(ISERROR(MID(B3,JÄRJESTUS(100),1)+0),MID(B3,JÄRJESTUS(100),1),""))) |
Kui vajutate sisestusklahvi, eemaldatakse kõik numbrimärgid ja alles on ainult tähestikulised väärtused. Lohistage see valem alla, et eemaldada numbrid stringist kõigist veeru B lahtritest.
Kuidas see töötab?
Kõigepealt vaatame, kuidas seda valemit samm -sammult lahendatakse.
| 1-> TEXTJOIN("",TÕSI,KUI(ISERROR(MID(B3,JÄRJESTUS(100),1)+0),MID(B3,JÄRJESTUS(100),1),""))) |
| 2-> TEXTJOIN("",TÕSI,KUI(ISERROR(MID("See1 on … sait",{1,2,3,… 100},1)+0),MID(B3,JÄRJESTUS(100),1),""))) |
| 3-> TEXTJOIN("",TÕSI,KUI(ISERROR({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MID(B3,JÄRJESTUS(100),1),""))) |
| 4-> TEXTJOIN("",TÕSI,KUI(ISERROR({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}),MID(B3,JÄRJESTUS(100),1),""))) |
| 5-> TEXTJOIN("",TÕSI,KUI({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MID(B3,JÄRJESTUS(100),1),""))) |
| 6-> TEXTJOIN("",TÕSI,{"T"; "h"; "i"; "s"; ""….; ""}) |
| 7-> "See on veebisait number üks" |
Lugemise hõlbustamiseks ei ole ma kogu massiivi kirjutanud. Pikkade massiivide tähistamiseks kasutasin punkte (…).
Nagu näete, hakkab valem lahendama seestpoolt. Alguses lahendatakse funktsioon SEQUENCE. Kuna oleme tähemärkide arvuna ületanud 100. See tagastab numbrimassiivi vahemikus 1 kuni 100.
Seda massiivi esitatakse MID -funktsioonile stardinumbrina. Keskfunktsioon läheb stringi igale indeksile ja jagab massiivi iga märgi. Näete seda 3. sammus. Ma pole kogu massiivi näidanud, kuna see võtab liiga palju ruumi. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}
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. {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}
Seda massiivi serveeritakse ISERROR funktsiooni. Nagu teate, tagastab funktsioon ISERROR vigade korral tõe ja mittevigade väärtuste puhul vale. Seega saame hulga tõde ja vale. TÕESE mittearvuliste tähemärkide ja valede numbrite puhul. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Sama juhtub IF (jao tõeliste jaotiste avaldustega (MID(B3,JÄRJESTUS(100),1)). See tagastab massiivi kõigi tähtede ja numbrite B3 tähemärkidest.
Nüüd tagastab iga tõdelause korral funktsioon IF vastava märgi tõelise sektsiooni massiivist. FALSE korral tagastab IF tühja (""). Nüüd on teil massiiv, mis ei sisalda ühtegi numbrimärki. {"T"; "h"; "i"; "s"; ""….; ""}.
Lõpuks teenindab seda massiivi TEXTJOIN funktsiooni. See funktsioon ühendab antud tekstid üksteisega, ignoreerides tühje väärtusi, antud eraldajaga. Seega saame stringi, mis ei sisalda ühtegi numbrimärki. See on veebisait number üks.
Valemi täiustamine
Ülaltoodud valem kasutab märkide arvu töötlemiseks kõvasti kodeeritud numbrit (võtsime 100). Kuid võite soovida, et see oleks dünaamiline. Sel juhul arvasite õigesti, saate kasutada funktsiooni LEN. Töötlemiseks kulub täpne tähemärkide arv. Nii et valem saab olema.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE (LEN(segamini_tekst), 1) +0), MID (segamini_tekst, SEQUENCE (LEN(segamini_tekst),1),""))) |
Siin tuvastab funktsioon LEN automaatselt tähtnumbrilise stringi täpsed numbrimärgid. See vabastab maksimaalse tähemärkide arvu määramise koormast.
Funktsiooni SEQUENCE alternatiiv
Kui te ei soovi funktsiooni SEQUENCE kasutada, saate järjestusnumbrite genereerimiseks kasutada funktsiooni ROW ja INDIRECT.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text,RIDA(KAUDSED("1:"&LEN(NumChars))), 1) +0), MID (segatud tekst,RIDA(KAUDSED("1:"&LEN(NumChars))),1),""))) |
INDIRECT teisendab teksti ("1: 100") tegelikuks vahemikuks ja seejärel loetleb funktsioon ROW kõik reanumbrid vahemikus 1 kuni 100. (100 on vaid näide. 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.
Mitte -numbriliste märkide eemaldamine Excel 2019 rakkudest: Mitte -numbriliste märkide eemaldamiseks Excelist tähtnumbrilisest stringist kasutame uut funktsiooni TEXTJOIN. Stringist mitte -numbriliste tähemärkide riba aitab meil andmeid paremaks analüüsimiseks puhastada. Nii et siin on, kuidas seda teha
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.