Kuinka jakaa numerot ja teksti merkkijonosta Excelissä

Usein saan erilaisia tietoja kentältä ja palvelimelta analysointia varten. Nämä tiedot ovat yleensä likaisia, ja sarake on sekoitettu numeroon ja tekstiin. Kun puhdistan tietoja ennen analysointia, erotan numerot ja tekstin erillisiin sarakkeisiin. Tässä artikkelissa kerron sinulle, kuinka voit tehdä sen.

Skenaario:
Joten yksi ystävämme Exceltip.comissa esitti tämän kysymyksen kommenttiosassa. "Kuinka erottaa tekstin edessä ja lopussa olevat numerot Excel -kaavan avulla. Esimerkiksi 125EvenueStreet ja LoveYou3000 jne. ”

Tekstin poimimiseksi käytämme OIKEA, VASEN, MID ja muita tekstitoimintoja. Meidän on vain tiedettävä otettavien tekstien määrä. Ja tässä teemme saman ensin.
Pura numero ja teksti merkkijonosta, kun numero on merkkijonon lopussa
Yllä olevassa esimerkissä olen laatinut tämän taulukon. Solussa A2 minulla on merkkijono. Solussa B2 haluan tekstiosan ja C2: ssa numeroosan.

Joten meidän tarvitsee vain tietää sijainti, josta numero alkaa. Sitten käytämme vasenta ja muuta toimintoa. Joten saadaksemme ensimmäisen numeron sijainnin käytämme alla olevaa yleistä kaavaa:
Yleinen kaava merkkijonon ensimmäisen sijainnin saamiseksi:

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

Tämä palauttaa ensimmäisen numeron sijainnin.
Yllä olevassa esimerkissä kirjoita tämä kaava mihin tahansa soluun.

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

Poimi tekstiosa

Se palauttaa 15, koska ensimmäinen numero, joka löytyy, on 15. sijalla tekstissä. Selitän sen myöhemmin.

Nyt saadaksesi tekstin, meidän tarvitsee vain saada 15-1 merkkiä merkkijonosta vasemmalta. Joten käytämme
VASEN -toiminto tekstin poimimiseksi.
Kaava tekstin poimimiseksi vasemmalta

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

Tässä olemme juuri vähentäneet yhden MIN: n palauttamasta numerosta (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Pura numeroosa

Nyt saadaksemme numeroita meidän täytyy vain saada numeromerkit löydetystä ensimmäisestä numerosta. Joten laskemme kokonaispituuden merkkijono ja vähennä ensimmäisen löydetyn numeron sijainti ja lisää 1 siihen. Yksinkertainen. Joo, se on vain monimutkaista, se on yksinkertaista.
Kaava poimia numerot oikealta

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

Täällä saimme juuri merkkijonon kokonaispituuden käyttämällä LEN -funktiota ja sitten vähennettiin ensimmäisen löydetyn numeron sijainti ja lisättiin sitten 1 siihen. Tämä antaa meille numeroiden kokonaismäärän. Lue lisää tekstin poimimisesta Excelin VASEN- ja OIKEA -funktioilla.

Joten VASEN ja OIKEA toiminnon osa on yksinkertainen. Hankala osa on MIN ja SEARCH -osa, joka antaa meille ensimmäisen löydetyn numeron sijainnin. Ymmärrämme sen.
Kuinka se toimii
Tiedämme kuinka VASEN ja OIKEA toimivat. Tutkimme tämän kaavan pääosaa, joka saa ensimmäisen numeron paikan ja joka on: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, merkkijono & "0123456789 ")
SEARCH -funktio palauttaa tekstin sijainnin merkkijonossa. HAKU ("teksti", "merkkijono") -toiminto ottaa kaksi argumenttia, ensin tekstin, jota haluat etsiä, ja sen jälkeen merkkijonon, josta haluat hakea.

- Täällä SEARCHissa, tekstiasennossa, meillä on joukko numeroita 0 - 9. Ja merkkijonon sijainnissa meillä on merkkijono, joka on yhdistetty "0123456789" - & operaattori. Miksi? Kerron sinulle.
- Jokaisesta taulukon {0,1,2,3,4,5,6,7,8,9} elementistä haetaan annetussa merkkijonossa ja se palauttaa sijaintinsa matriisimuodossa merkkijonossa taulukon samassa indeksissä.
- Jos arvoa ei löydy, se aiheuttaa virheen. Siksi kaikki kaavat johtavat virheeseen. Tämän välttämiseksi yhdistämme tekstissä numerot "0123456789". Joten se löytää aina jokaisen numeron merkkijonosta. Nämä luvut ovat lopulta, joten ne eivät aiheuta ongelmia.
- Nyt MIN -funktio palauttaa pienimmän arvon SEARCH -funktion palauttamasta taulukosta. Tämä pienin arvo on merkkijonon ensimmäinen numero. Nyt käyttämällä tätä NUMBER- ja VASEN- ja OIKEA -toimintoa, voimme jakaa teksti- ja merkkijono -osat.

Tarkastellaan esimerkkiämme. A5: ssa on merkkijono, jolla on kadun nimi ja talon numero. Meidän on erotettava ne eri soluista.
Katsotaan ensin, miten saimme merkkijonon ensimmäisen numeron.

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

Nyt, kuten selitin, haku etsii jokaista numeroa taulukosta {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 ja palauttaa sijaintinsa taulukkomuodossa. Palautettu taulukko on {8,9,6,11,12,13,14,7,16,17}. Miten?
0 haetaan merkkijonona. Se löytyy sijainnista 8. Siksi ensimmäinen elementti on 8. Huomaa, että alkuperäinen tekstimme on vain 7 merkkiä pitkä. Ota se. 0 ei ole osa Monta27.
Seuraava 1 haetaan merkkijonona, eikä se myöskään ole osa alkuperäistä merkkijonoa, ja saamme sen sijainnin 9.
Seuraavat 2 haetaan. Koska se on osa alkuperäistä merkkijonoa, saamme sen indeksin 6.
Samoin jokainen elementti löytyy jostakin paikasta.

- Nyt tämä taulukko välitetään MIN -funktiolle MIN -muodossa ({8,9,6,11,12,13,14,7,16,17}). MIN palauttaa 6, joka on alkuperäisen tekstin ensimmäisen numeron sijainti.
  Ja tarina tämän jälkeen on melko yksinkertainen. Käytämme tätä numerouutteen tekstiä ja numeroita käyttämällä VASEN- ja OIKEA -toimintoa.

Pura numero ja teksti merkkijonosta, kun numero on merkkijonon alussa
Yllä olevassa esimerkissä numero oli merkkijonon lopussa. Kuinka otamme numeron ja tekstin, kun numero on alussa.

Olen laatinut samanlaisen taulukon kuin yllä. Siinä on vain numero alussa.

Tässä käytämme eri tekniikkaa. Laskemme numeroiden pituuden (joka on 2 tässä) ja poimimme sen merkkimäärän merkkijonon vasemmalta puolelta.
Menetelmä on siis = LEFT (merkkijono, numeroiden määrä)
Merkkien määrän laskemiseksi tämä on kaava.
Yleinen kaava numeroiden laskemiseen:

= SUMMA (LEN (merkkijono) -LEN (KORVAUS (merkkijono, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Tässä,

- - SUBSTITUTE -toiminto korvaa jokaisen löydetyn numeron “” (tyhjä). Jos numero löydetään ti korvattuna ja uusi merkkijono lisätään taulukkoon, muu viisas alkuperäinen merkkijono lisätään taulukkoon. Tällä tavalla meillä on 10 merkkijonoa.
  - Nyt LEN -funktio palauttaa merkkien pituuden näiden merkkijonojen ryhmässä.
  - Sitten vähennämme alkuperäisten merkkijonojen pituudesta jokaisen SUBSTITUTE -funktion palauttaman merkkijonon pituuden. Tämä palauttaa jälleen taulukon.
  - Nyt SUM lisää kaikki nämä numerot. Tämä on merkkijonossa olevien numeroiden määrä.

Pura numeroosa merkkijonosta

Nyt kun tiedämme numeroiden pituuden merkkijono, korvaamme tämän toiminnon VASEMMALLA.
Koska meillä on merkkijono A11, meidän:

Kaava poimia numeroita VASEMMALLE

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

Pura tekstiosa merkkijonosta

Koska tiedämme numeroiden lukumäärän, voimme vähentää sen merkkijonon kokonaispituudesta saadaksemme numeroaakkoset merkkijonosta ja käyttämällä sitten oikeaa funktiota poistaaksemme kyseisen merkkimäärän merkkijonon oikealta puolelta.

Kaava tekstin poimimiseksi OIKEALTA

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

Kuinka se toimii
Molempien kaavojen pääosa on SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) joka laskee luvun ensimmäisen esiintymisen. Vasta tämän löydettyämme voimme jakaa tekstin ja numerot LEFT -toiminnolla. Joten ymmärrämme tämän.

- - KORVAUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Tämä osa palauttaa merkkijonon A11: ssä sen jälkeen, kun nämä numerot on korvattu tyhjällä/tyhjällä (“”). Varten 27Monta se palauttaa {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
  - LENSSI (VAIHTOEHTO (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Nyt KORVAUS -osa on kääritty LEN -toiminnolla. Tämä taulukon tekstien palautuspituus, jonka palauttaa SUBSTITUTE -funktio. Tuloksena on {7,7,6,7,7,7,7,6,7,7}.
  - LEN (A11) -LEN (KORVAUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," ")): Tässä vähennämme jokaisen yläosan palauttaman numeron todellisen merkkijonon pituudesta. Alkuperäisen tekstin pituus on 7. Siksi meillä on {7-7,7-7,7-6,….}. Lopuksi meillä on {0,0,1,0,0,0,0,1,0,0}.
  - SUMMA (LEN (A11) -LEN (KORVAUS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Tässä käytimme SUM -funktiota summaamaan funktion yllä olevan osan palauttama taulukko. Tämä antaa 2. Mikä on merkkijonossa olevien numeroiden määrä.

Nyt tämän avulla voimme poimia tekstit ja numerot ja jakaa ne eri soluihin. Tämä menetelmä toimii sekä tekstityypin kanssa, kun numero on alussa ja kun se on lopussa. Sinun tarvitsee vain käyttää vasenta ja oikeaa toimintoa oikein.
SplitNumText -toiminnon avulla voit jakaa numerot ja tekstit merkkijonosta
Edellä esitetyt menetelmät ovat hieman monimutkaisia, eivätkä ne ole hyödyllisiä, kun tekstiä ja numeroita sekoitetaan. Käytä tätä käyttäjän määrittämää toimintoa tekstin ja numeroiden jakamiseen.

Syntaksi:

= SplitNumText (merkkijono, op)

Jono: Merkkijono, jonka haluat jakaa.
Op: tämä on boolean. Pass 0 tai väärä saada tekstiosa. Numeroosalle, läpäise totta tai mikä tahansa luku, joka on suurempi kuin 0.
Jos merkkijono on esimerkiksi A20,
Kaava numeroiden erottamiseksi merkkijonosta on:

= SplitNumText (A20,1)

Ja
Kaava tekstin erottamiseksi merkkijonosta on:

= SplitNumText (A20,0)

Kopioi alla oleva koodi VBA -moduulissa saadaksesi yllä olevan kaavan toimimaan.

Funktio SplitNumText (str As String, op Booan) num = "" txt = "" For i = 1 to Len (str) If IsNumeric (Mid (str, i, 1)) Sitten num = num & Mid (str, i , 1) Muu txt = txt & Mid (str, i, 1) Lopeta Jos Seuraava i Jos op = Tosi Sitten SplitNumText = numero Muu SplitNumText = txt Lopeta Jos Lopeta -toiminto

Tämä koodi yksinkertaisesti tarkistaa jokaisen merkkijonon, onko se numero vai ei. Jos se on numero, se tallennetaan num -muuttujaan muu txt -muuttujaan. Jos käyttäjä antaa arvon true op: lle, numero palautetaan, muuten txt palautetaan.

Tämä on mielestäni paras tapa jakaa numero ja teksti merkkijonosta.
Voit halutessasi ladata työkirjan täältä.

Joten kyllä kaverit, nämä ovat tapoja jakaa teksti ja numerot eri soluihin. Kerro minulle, jos sinulla on epäilyksiä tai parempi ratkaisu alla olevissa kommenttien osassa. On aina hauskaa olla kavereiden kanssa tekemisissä.

Lataa työtiedosto napsauttamalla alla olevaa linkkiä:

Jaa luku ja teksti solusta

Suosittuja artikkeleita:
50 Excel -pikakuvaketta tuottavuuden lisäämiseksi
VLOOKUP -toiminto Excelissä
COUNTIF Excel 2016: ssa
SUMIF -toiminnon käyttäminen Excelissä