Kuidas Excelis numbreid ja teksti stringidest jagada

Mitu korda saan analüüsimiseks väljalt ja serverilt segaandmeid. Need andmed on tavaliselt määrdunud, veerg on segatud numbri ja tekstiga. Andmeid enne analüüsi puhastades eraldan numbrid ja teksti eraldi veergudesse. Selles artiklis räägin teile, kuidas saate seda teha.

Stsenaarium:
Nii et üks meie sõber saidil Exceltip.com esitas selle küsimuse kommentaaride jaotises. „Kuidas eraldada Exceli valemi abil numbreid, mis tulevad teksti ette ja teksti lõppu? Näiteks 125EvenueStreet ja LoveYou3000 jne. ”

Teksti ekstraheerimiseks kasutame parem-, vasak-, kesk- ja muid tekstifunktsioone. Peame lihtsalt teadma väljavõetavate tekstide arvu. Ja siin teeme kõigepealt sama.
Ekstraheerige number ja tekst stringist, kui number on stringi lõpus
Ülaltoodud näite jaoks olen selle lehe ette valmistanud. Lahtris A2 on mul string. Lahtris B2 tahan tekstiosa ja C2 -s numbriosa.

Seega peame lihtsalt teadma positsiooni, kust number algab. Siis kasutame vasakut ja muud funktsiooni. Nii et esimese numbri positsiooni saamiseks kasutame allpool üldvalemit:
Üldvalem stringi esimese numbri positsiooni saamiseks:

= MIN (OTSI ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

See tagastab esimese numbri positsiooni.
Ülaltoodud näite jaoks kirjutage see valem suvalisse lahtrisse.

= MIN (OTSI ({0,1,2,3,4,5,6,7,8,9}, A5 ja "0123456789"))

Väljavõtte tekstiosa

See tagastab 15, kuna esimene number, mis leitakse, asub teksti 15. kohal. Ma selgitan seda hiljem.

Nüüd, teksti saamiseks peame vasakult lihtsalt stringist saama 15-1 tähemärki. Nii et kasutame
Funktsioon LEFT teksti ekstraheerimiseks.
Valem teksti ekstraheerimiseks vasakult

= LEFT (A5, MIN (OTSING ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)

Siin lahutasime lihtsalt 1 MIN -i tagastatud numbrist (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 ja "0123456789")).
Väljavõtte number osa

Nüüd numbrite saamiseks peame lihtsalt leidma numbrimärgid leitud numbrist. Seega arvutame kogupikkuse string ja lahutage leitud esimese numbri asukoht ja lisage 1 sellele. Lihtne. Jah, see on lihtsalt keeruline, see on lihtne.
Valem numbrite eraldamiseks paremalt

= PAREM (A5, LEN (A5) -MIN (OTSING ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))+1)

Siin saime lihtsalt LEN -funktsiooni abil stringi kogupikkuse ja lahutasime seejärel leitud numbri positsiooni ja lisasime sellele 1. See annab meile numbrite koguarvu. Siit saate lisateavet teksti ekstraheerimise kohta Exceli LEFT ja RIGHT funktsioonide abil.

Nii et funktsiooni LEFT ja RIGHT osa on lihtne. Keeruline osa on MIN ja OTSI osa, mis annab meile esimese leitud numbri asukoha. Mõistame seda.
Kuidas see töötab
Me teame, kuidas LEFT ja RIGHT funktsioonid töötavad. Uurime selle valemi põhiosa, mis saab leitud esimese numbri positsiooni ja see on: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Funktsioon SEARCH tagastab stringi teksti asukoha. Funktsioon SEARCH („tekst”, „string”) võtab kaks argumenti, esiteks teksti, mida soovite otsida, ja teiseks stringi, milles soovite otsida.

- Siin otsingus on tekstiasendis numbrimassiiv vahemikus 0 kuni 9. Ja stringi asendis on meil string, mis on ühendatud "0123456789" abil & operaator. Miks? Ma ütlen teile.
- Massiivi {0,1,2,3,4,5,6,7,8,9} igast elemendist otsitakse antud stringis ja see tagastab oma positsiooni massiivivormi stringis massiivi samas indeksis.
- Kui väärtust ei leita, põhjustab see tõrke. Seega toob kogu valem kaasa vea. Selle vältimiseks ühendasime tekstis numbrid "0123456789". Nii et see leiab alati iga numbri stringist. Need numbrid on lõpuks seega ei tekita probleeme.
- Funktsioon MIN tagastab väikseima väärtuse funktsioonist SEARCH tagastatud massiivist. See väikseim väärtus on stringi esimene number. Kasutades seda funktsiooni NUMBER ning LEFT ja RIGHT, saame teksti- ja stringiosad jagada.

Uurime meie näidet. A5 -s on meil string, millel on tänavanimi ja maja number. Peame need erinevatesse lahtritesse eraldama.
Kõigepealt vaatame, kuidas saime stringi esimese numbri positsiooni.

- MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): see tähendab MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9} ”Monta270123456789”))

Nüüd, nagu ma selgitasin, otsib otsing kõiki massiivi numbreid {0, 1, 2, 3, 4, 5, 6, 7, 8, 9 tolli Monta270123456789 ja tagastab oma positsiooni massiivi kujul. Tagastatud massiiv on {8,9,6,11,12,13,14,7,16,17}. Kuidas?
0 otsitakse stringina. Seda leidub positsioonil 8. Seega on meie esimene element 8. Pange tähele, et meie algtekst on vaid 7 tähemärki pikk. Saage kätte. 0 ei ole osa Monta27.
Järgmist 1 otsitakse stringina ja see ei ole ka algse stringi osa ning saame selle positsiooni 9.
Järgmised 2 otsitakse läbi. Kuna see on algse stringi osa, saame selle indeksi 6.
Sarnaselt leidub iga element teatud asendis.

- Nüüd edastatakse see massiiv MIN funktsioonile MIN ({8,9,6,11,12,13,14,7,16,17}). MIN tagastab 6, mis on algtekstis leitud esimese numbri asukoht.
  Ja lugu pärast seda on üsna lihtne. Me kasutame seda numbrite väljavõtte teksti ja numbreid, kasutades funktsiooni LEFT ja RIGHT.

Ekstraheerige number ja tekst stringist, kui number on stringi alguses
Ülaltoodud näites oli number stringi lõpus. Kuidas me numbrit ja teksti eraldame, kui number on alguses.

Olen koostanud sarnase tabeli nagu eespool. Algul on lihtsalt number.

Siin kasutame erinevat tehnikat. Loendame numbrite pikkuse (mis on siin 2) ja eraldame selle arvu märke stringist vasakult.
Seega on meetod = LEFT (string, numbrite arv)
Märkide arvu loendamiseks on see valem.
Üldvalem numbrite arvu loendamiseks:

= SUM (LEN (string) -LEN (SUBSTITUTE (string, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Siin,

- - Funktsioon SUBSTITUTE asendab iga leitud numbri tähega “” (tühi). Kui number leitakse asendatuna ja massiivi lisatakse uus string, lisatakse massiivi muu tark algne string. Sel viisil saame massiivi 10 stringist.
  - Nüüd tagas funktsioon LEN nende stringide massiivi märkide pikkuse.
  - Seejärel lahutame algsete stringide pikkusest iga funktsiooni SUBSTITUTE tagastatud stringi pikkuse. See tagastab uuesti massiivi.
  - Nüüd lisab SUM kõik need numbrid. See on numbrite arv stringis.

Väljavõtte number osa stringist

Nüüd, kui me teame numbrite pikkust string, asendame selle funktsiooni vasakul.
Kuna meil on meie string A11, siis:

Valem numbrite väljavõtmiseks vasakult

= LEFT (A11, SUM (LEN (A11) -LEN (ASENDUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))

Väljavõte tekstist stringist

Kuna me teame numbrite arvu, saame selle stringide kogupikkusest lahutada, et saada numbritähestikke ja seejärel kasutada paremat funktsiooni, et eraldada see tähemärkide arv paremast stringist.

Valem teksti väljavõtmiseks paremalt

= PAREM (A11, LEN (A2) -SUM (LEN (A11) -LEN (ASENDUS (A11, {"0", "1", "2", "3", "4", "5", "6) "," 7 "," 8 "," 9 "}," "))))

Kuidas see töötab
Mõlema valemi põhiosa on SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))), mis arvutab arvu esimese esinemise. Alles pärast selle leidmist saame teksti ja numbreid LEFT funktsiooni abil jagada. Nii et mõistame seda.

- - ASENDUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): See osa tagastab A11 stringide massiivi pärast nende numbrite asendamist tühiku/tühjaga (“”). Sest 27Monta see tagastab {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
  - LEN (ASENDUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Nüüd asendatakse LEN -funktsiooniga asendusosa. See massiivi tekstide tagasipikkus tagastati funktsiooni SUBSTITUTE abil. Selle tulemusel saame {7,7,6,7,7,7,7,6,7,7}.
  - LEN (A11) -LEN (ASENDUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9" "}," ")): Siin lahutame tegeliku stringi pikkusest iga ülaltoodud arvu tagastatud arvu. Originaalteksti pikkus on 7. Seega on meil {7-7,7-7,7-6,….}. Lõpuks on meil {0,0,1,0,0,0,0,1,0,0}.
  - SUM (LEN (A11) -LEN (ASENDUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Siin kasutasime funktsiooni Ülaosa tagastatud massiivi summeerimiseks summat SUM. See annab 2. Mis on numbrite arv stringis.

Nüüd saame seda kasutades tekste ja numbreid ekstraheerida ning jagada erinevatesse lahtritesse. See meetod töötab mõlemat tüüpi tekstiga, kui number on alguses ja millal see on lõpus. Peate lihtsalt kasutama vasaku ja parema funktsiooni.
Kasutage funktsiooni SplitNumText numbrite ja tekstide eraldamiseks stringist
Ülaltoodud meetodid on natuke keerulised ja need ei ole kasulikud teksti ja numbrite segamisel. Teksti ja numbrite jagamiseks kasutage seda kasutaja määratud funktsiooni.

Süntaks:

= SplitNumText (string, op)

String: String, mida soovite jagada.
Op: see on loogiline. Pass 0 või vale tekstiosa saamiseks. Numbriosa jaoks läbige tõsi või mis tahes arv on suurem kui 0.
Näiteks kui string on A20 -s,
Valem numbrite eraldamiseks stringist on järgmine:

= SplitNumText (A20,1)

Ja
Tekstist stringist ekstraheerimise valem on järgmine:

= SplitNumText (A20,0)

Kopeerige allolev kood VBA moodulis, et ülaltoodud valem toimiks.

Funktsioon SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 to Len (str) If IsNumeric (Mid (str, i, 1)) Siis num = num & Mid (str, i , 1) Muu txt = txt & Mid (str, i, 1) End If Next i Kui op = True Siis SplitNumText = num Muu SplitNumText = txt End If End funktsioon

See kood lihtsalt kontrollib stringi iga märki, kas see on number või mitte. Kui see on number, salvestatakse see muutuja num muut, muu txt muutuja. Kui kasutaja annab op jaoks tõene, tagastatakse number, muidu tagastatakse txt.

See on minu arvates parim viis numbrite ja tekstide eraldamiseks stringist.
Soovi korral saate töövihiku siit alla laadida.

Nii et jah, poisid, need on viisid teksti ja numbrite jagamiseks erinevatesse lahtritesse. Andke mulle teada, kui teil on kahtlusi või mõni parem lahendus allpool olevas kommentaaride jaotises. Poistega on alati lõbus suhelda.

Tööfaili allalaadimiseks klõpsake alloleval lingil:

Jagage arv ja tekst lahtrist

Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks
Funktsioon VLOOKUP Excelis
COUNTIF Excelis 2016
Funktsiooni SUMIF kasutamine Excelis