Kako ločiti številke in besedilo iz niza v Excelu

Anonim

Velikokrat dobim mešane podatke s terena in strežnika za analizo. Ti podatki so običajno umazani in imajo stolpec pomešan s številom in besedilom. Med čiščenjem podatkov pred analizo ločim številke in besedilo v ločene stolpce. V tem članku vam bom povedal, kako lahko to storite.

Scenarij:
Tako je en naš prijatelj na spletnem mestu Exceltip.com to vprašanje postavil v razdelku s komentarji. »Kako ločim številke pred besedilom in na koncu besedila z uporabo Excelove formule. Na primer 125EvenueStreet in LoveYou3000 itd. "

Za ekstrahiranje besedila uporabljamo DESNO, LEVO, MID in druge besedilne funkcije. Vedeti moramo le, koliko besedil moramo izvleči. In tukaj bomo najprej storili enako.
Izvlecite številko in besedilo iz niza, ko je število na koncu niza
Za zgornji primer sem pripravil ta list. V celici A2 imam niz. V celici B2 želim besedilni del, v C2 pa številčni del.

Zato moramo samo vedeti, kje se številka začne. Nato bomo uporabili Levo in druge funkcije. Torej, da dobimo položaj prve številke, uporabimo spodaj splošno formulo:
Splošna formula za prvo mesto v nizu:

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

S tem se vrne položaj prve številke.
Za zgornji primer vnesite to formulo v katero koli celico.

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

Del izvlečka besedila

Vrnil bo 15, saj je prva najdena številka na 15. mestu v besedilu. Bom razložil kasneje.

Zdaj, da dobimo Text, moramo levo dobiti 15-1 znak iz niza. Zato bomo uporabili
LEFT funkcija za izvlečenje besedila.
Formula za izvlečenje besedila z leve

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


Tu smo pravkar odšteli 1 od števila, ki ga je vrnilo MIN (ISKANJE ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Del izvlečka

Zdaj, da dobimo številke, moramo le dobiti številske znake iz prve najdene številke. Tako izračunamo skupno dolžino vrvica ter odštejte položaj prve najdene številke in seštejte 1 temu. Enostavno. Ja, samo zveni zapleteno, preprosto je.
Formula za ekstrahiranje številk z desne

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

Tukaj smo s funkcijo LEN pravkar dobili skupno dolžino niza, nato pa odšteli položaj prve najdene številke in ji nato dodali 1. To nam daje skupno število številk. Več o ekstrahiranju besedila z uporabo leve in desne funkcije Excela tukaj.

Tako sta funkcijski del LEVO in DESNO preprost. Zapleten del je del MIN in ISKANJE, ki nam daje položaj prve najdene številke. Razumejmo to.
Kako deluje
Vemo, kako delujeta funkcija LEFT in RIGHT. Raziskovali bomo glavni del te formule, ki dobi položaj prve najdene številke in je: MIN (ISKANJE ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Funkcija SEARCH vrne položaj besedila v nizu. SEARCH ("besedilo", "niz") ima dva argumenta, najprej besedilo, ki ga želite iskati, drugi niz, v katerem želite iskati.

    • Tukaj, v ISKALNIKU, na mestu besedila imamo niz številk od 0 do 9. Na mestu niza pa imamo niz, ki je povezan z "0123456789" z uporabo & operater. Zakaj? Povedal ti bom.
    • Vsak element v matriki {0,1,2,3,4,5,6,7,8,9} bo iskan v danem nizu in bo vrnil svoj položaj v nizu oblike matrike z istim indeksom v matriki.
    • Če nobene vrednosti ne najdete, bo to povzročilo napako. Zato bo vsa formula povzročila napako. Da bi se temu izognili, smo v besedilu združili številke "0123456789". Tako, da vedno najde vsako številko v nizu. Na koncu te številke ne bodo povzročale težav.
    • Zdaj funkcija MIN vrne najmanjšo vrednost iz matrike, ki jo vrne funkcija SEARCH. Ta najmanjša vrednost bo prva številka v nizu. S pomočjo te funkcije NUMBER in LEFT in RIGHT lahko delimo besedilo in dele niza.

Poglejmo naš primer. V A5 imamo niz z imenom ulice in hišno številko. Ločiti jih moramo v različnih celicah.
Najprej poglejmo, kako smo v nizu dobili prvo mesto.

    • MIN (ISKANJE ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): to se bo prevedlo v MIN (ISKANJE ({0,1,2,3, 4,5,6,7,8,9}, "Monta270123456789”))

Zdaj, kot sem pojasnil, bo iskanje iskalo vsako številko v matriki {0,1,2,3,4,5,6,7,8,9} v Monta270123456789 in bo vrnil svoj položaj v obliki matrike. Vrnjeni niz bo {8,9,6,11,12,13,14,7,16,17}. Kako?
0 bo iskan v nizu. Najdemo ga na položaju 8. Zato je naš prvi element 8. Upoštevajte, da je naše izvirno besedilo dolgo le 7 znakov. Razumeš. 0 ni del Monta27.
Naslednji 1 bo iskan v nizu in prav tako ni del prvotnega niza, dobimo pa položaj 9.
Naslednji 2 bo iskan. Ker je del izvirnega niza, dobimo njegov indeks 6.
Podobno se vsak element nahaja na določenem mestu.

    • Zdaj se ta niz prenese v funkcijo MIN kot MIN ({8,9,6,11,12,13,14,7,16,17}). MIN vrne 6, ki je položaj prve številke v izvirnem besedilu.
      In zgodba po tem je precej preprosta. To število uporabljamo za izvlečenje besedila in številk s funkcijo LEVO in DESNO.

Izvlecite številko in besedilo iz niza, ko je število na začetku niza
V zgornjem primeru je bila številka na koncu niza. Kako izvlečemo številko in besedilo, ko je številka na začetku.

Pripravil sem podobno tabelo kot zgoraj. Na začetku ima samo številko.

Tu bomo uporabili drugačno tehniko. Prešteli bomo dolžino števil (tukaj je 2) in to število znakov izvlekli iz leve strani niza.
Torej je metoda = LEFT (niz, število številk)
Za štetje števila znakov je to formula.
Splošna formula za štetje števila:

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

Tukaj,

      • Funkcija SUBSTITUTE bo vsako najdeno številko nadomestila z “” (prazno). Če najdete številko, ki je nadomeščena, v matriko pa bo dodan nov niz, bo matriki dodan drug pameten izvirni niz. Na ta način bomo imeli niz 10 nizov.
      • Zdaj bo funkcija LEN vrnila dolžino znakov v nizu teh nizov.
      • Nato bomo od dolžine izvirnih nizov odšteli dolžino vsakega niza, ki ga vrne funkcija SUBSTITUTE. To bo spet vrnilo matriko.
      • Zdaj bo SUM dodal vse te številke. To je število števil v nizu.

Izvleči del številke iz niza

Zdaj, ko poznamo dolžino števil v vrvica, to funkcijo bomo zamenjali v LEVI.
Ker imamo svoj niz A11 naš:

Formula za ekstrahiranje številk iz LEFT

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


Izvleči del besedila iz niza

Ker poznamo število številk, ga lahko odštejemo od skupne dolžine niza, da dobimo številske abecede v nizu, nato pa z desno funkcijo izvlečemo to število znakov z desne strani niza.

Formula za ekstrahiranje besedila iz DESNO

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


Kako deluje
Glavni del obeh formul je SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))), ki izračuna prvi pojav števila. Šele ko to ugotovimo, lahko razdelimo besedilo in številko s funkcijo LEVO. Zato razumejmo to.

      • ZAMENJA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Ta del vrne niz nizov v A11, potem ko te številke zamenjate z nič/prazno (“”). Za 27Monta vrnil bo {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
      • LEN (ZAMENJIVA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Zdaj je del SUBSTITUTE zavit s funkcijo LEN. Ta dolžina vračila besedil v matriki, ki jih vrne funkcija SUBSTITUTE. Posledično bomo imeli {7,7,6,7,7,7,7,6,7,7}.
      • LEN (A11) -LEN (ZAMENJALNIK (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," "))): Tukaj odštejemo vsako število, ki ga vrne zgornji del, od dolžine dejanskega niza. Dolžina izvirnega besedila je 7. Zato bomo imeli {7-7,7-7,7-6,….}. Končno bomo imeli {0,0,1,0,0,0,0,1,0,0}.
      • SUM (LEN (A11) -LEN (ZAMENJA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Tukaj smo uporabili SUM za seštevanje matrike, ki jo je vrnil zgornji del funkcije. To bo dalo 2. To je število števil v nizu.

Zdaj lahko s tem izvlečemo besedila in številke ter jih razdelimo v različne celice. Ta metoda bo delovala z obema vrstama besedila, ko je številka na začetku in ko je na koncu. Morate le ustrezno uporabiti funkcijo LEFT in RIGHT.
Za razdelitev števil in besedil iz niza uporabite funkcijo SplitNumText
Zgornje metode so nekoliko zapletene in niso uporabne pri mešanju besedila in številk. Za razdelitev besedila in številk uporabite to uporabniško določeno funkcijo.

Sintaksa:

= SplitNumText (niz, op)

Vrvica: Niz, ki ga želite razdeliti.
Op: to je logično. Podajte 0 oz napačno da dobite del besedila. Za številčni del podajte prav ali katero koli število večje od 0.
Na primer, če je niz v A20, potem
Formula za izvlečenje števil iz niza je:

= SplitNumText (A20,1)

In
Formula za izvlečenje besedila iz niza je:

= SplitNumText (A20,0)

Kopirajte spodnjo kodo v modul VBA, da bo zgornja formula delovala.

Funkcija SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Potem je num = num & Mid (str, i , 1) V nasprotnem primeru txt = txt & Mid (str, i, 1) End If Next i If op = True Potem SplitNumText = num Else SplitNumText = txt End If End Funkcija

Ta koda preprosto preveri vsak znak v nizu, če je njegova številka ali ne. Če je številka, je shranjena v spremenljivki num, sicer v spremenljivki txt. Če uporabnik poda op za true, se vrne num, sicer se vrne txt.

To je po mojem mnenju najboljši način za ločitev števila in besedila iz niza.
Če želite, lahko delovni zvezek prenesete tukaj.

Torej, fantje, to so načini za razdelitev besedila in številk v različne celice. Sporočite mi, če imate dvome ali kakšno boljšo rešitev v spodnjem oddelku za komentarje. Vedno je zabavno komunicirati s fanti.

Kliknite spodnjo povezavo za prenos delovne datoteke:

Razdelite številko in besedilo iz celice

Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost
Funkcija VLOOKUP v Excelu
COUNTIF v Excelu 2016
Kako uporabljati funkcijo SUMIF v Excelu