Kako razdvojiti brojeve i tekst iz niza u Excelu

Anonim

Mnogo puta dobivam mješovite podatke s terena i poslužitelja za analizu. Ti su podaci obično prljavi, a stupac je pomiješan s brojem i tekstom. Čišćenjem podataka prije analize odvajam brojeve i tekst u zasebne stupce. U ovom članku ću vam reći kako to možete učiniti.

Scenarij:
Tako je jedan naš prijatelj na Exceltip.com postavio ovo pitanje u odjeljku komentara. “Kako odvojiti brojeve koji dolaze prije teksta i na kraju teksta pomoću Excelove formule. Na primjer 125EvenueStreet i LoveYou3000 itd. "

Za izdvajanje teksta koristimo DESNO, LIJEVO, MID i druge funkcije teksta. Moramo samo znati broj tekstova za izdvajanje. I ovdje ćemo prvo učiniti isto.
Izdvojite broj i tekst iz niza kada je broj na kraju niza
Za gornji primjer pripremio sam ovaj list. U ćeliji A2 imam niz. U ćeliji B2 želim tekstualni dio, a u C2 brojčani dio.

Zato samo trebamo znati odakle počinje broj. Tada ćemo koristiti Lijevo i druge funkcije. Dakle, za dobivanje pozicije prvog broja koristimo donju generičku formulu:
Opća formula za dobivanje pozicije prvog broja u nizu:

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

Ovo će vratiti položaj prvog broja.
Za gornji primjer upišite ovu formulu u bilo koju ćeliju.

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

Dio izdvajanja teksta

Vratit će 15 jer je prvi pronađeni broj na 15. mjestu u tekstu. Objasnit ću to kasnije.

Sada, da bismo dobili Text, s lijeve strane samo trebamo dobiti 15-1 znak iz niza. Zato ćemo koristiti
Lijeva funkcija za izdvajanje teksta.
Formula za izdvajanje teksta s lijeve strane

= LIJEVO (A5, MIN (TRAŽI ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)


Ovdje smo upravo oduzeli 1 od bilo kojeg broja koji je vratio MIN (PRETRAGA ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Dio izvoda

Sada za dobivanje brojeva samo moramo dobiti znakove broja od prvog pronađenog broja. Tako izračunavamo ukupnu duljinu niz te oduzeti položaj prvog pronađenog broja i zbrajati 1 na to. Jednostavan. Da, samo zvuči složeno, jednostavno je.
Formula za izdvajanje brojeva s desne strane

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

Ovdje smo upravo dobili ukupnu duljinu niza pomoću funkcije LEN, a zatim smo oduzeli položaj prvo pronađenog broja, a zatim mu dodali 1. To nam daje ukupan broj brojeva. Ovdje saznajte više o izdvajanju teksta pomoću funkcija LIJEVO i DESNO u Excelu.

Dakle, dio funkcije LIJEVO i DESNO je jednostavan. Lukav dio je MIN i TRAŽI dio koji nam daje poziciju prvog pronađenog broja. Shvatimo to.
Kako radi
Znamo kako funkcioniraju funkcija LIJEVO i DESNO. Istražit ćemo glavni dio ove formule koji dobiva poziciju prvog pronađenog broja, a to je: MIN (PRETRAGA ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Funkcija SEARCH vraća poziciju teksta u nizu. SEARCH ('text', 'string') funkcija ima dva argumenta, prvo tekst koji želite pretraživati, drugi niz u kojem želite pretraživati.

    • Ovdje u SEARCH -u na položaju teksta imamo niz brojeva od 0 do 9. A na položaju niza imamo niz koji je spojen s "0123456789" pomoću & operater. Zašto? Reći ću ti.
    • Svaki element u nizu {0,1,2,3,4,5,6,7,8,9} bit će pretražen u danom nizu i vratit će svoju poziciju u nizu u obliku niza s istim indeksom u nizu.
    • Ako bilo koja vrijednost nije pronađena, uzrokovat će pogrešku. Stoga će sve formule rezultirati pogreškom. Da bismo to izbjegli, spojili smo brojeve "0123456789" u tekstu. Tako da uvijek pronađe svaki broj u nizu. Ovi brojevi su na kraju stoga neće uzrokovati nikakve probleme.
    • Sada funkcija MIN vraća najmanju vrijednost iz niza koju vraća funkcija SEARCH. Ova najmanja vrijednost bit će prvi broj u nizu. Sada pomoću ove BROJKE i LIJEVO i DESNO možemo podijeliti dijelove teksta i niza.

Pogledajmo naš primjer. U A5 imamo niz koji ima naziv ulice i kućni broj. Moramo ih razdvojiti u različite ćelije.
Prvo da vidimo kako smo dobili naš prvi položaj u nizu.

    • MIN (PRETRAGA ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): ovo će se prevesti u MIN (PRETRAGA ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Kao što sam objasnio, pretraživanje će pretraživati ​​svaki broj u nizu {0,1,2,3,4,5,6,7,8,9} u Monta270123456789 i vratit će svoju poziciju u obliku polja. Vraćeni niz bit će {8,9,6,11,12,13,14,7,16,17}. Kako?
0 će se tražiti u nizu. Nalazi se na 8 poziciji. Stoga je naš prvi element 8. Imajte na umu da je naš izvorni tekst dugačak samo 7 znakova. Uzmi. 0 nije dio Monta27.
Sljedeći 1 će se tražiti u nizu i također nije dio izvornog niza, a dobivamo njegovu poziciju 9.
Sljedeća 2 će se pretraživati. Budući da je dio izvornog niza, dobivamo njegov indeks kao 6.
Slično se svaki element nalazi na nekom položaju.

    • Sada se ovaj niz prosljeđuje funkciji MIN kao MIN ({8,9,6,11,12,13,14,7,16,17}). MIN vraća 6 koji je položaj prvog broja u izvornom tekstu.
      A priča nakon ovoga prilično je jednostavna. Ovaj broj koristimo za izdvajanje teksta i brojeva pomoću funkcija LIJEVO i DESNO.

Izdvajanje broja i teksta iz niza kada je broj na početku niza
U gornjem primjeru, Number je bio na kraju niza. Kako izdvojiti broj i tekst kad je broj na početku.

Pripremio sam sličnu tablicu kao gore. Ima samo broj na početku.

Ovdje ćemo koristiti drugačiju tehniku. Brojat ćemo duljinu brojeva (što je 2 ovdje) i izdvojit ćemo taj broj znakova s ​​lijeve strane niza.
Dakle, metoda je = LIJEVO (niz, broj brojeva)
Za brojanje broja znakova ovo je formula.
Opća formula za brojanje brojeva:

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

Ovdje,

      • ZAMJENA će zamijeniti svaki pronađeni broj sa “” (prazno). Ako se pronađe broj zamijenjen i novi niz će se dodati u niz, drugi mudri izvorni niz bit će dodan u niz. Na ovaj način imat ćemo niz od 10 nizova.
      • Sada će funkcija LEN vratiti duljinu znakova u nizu tih nizova.
      • Zatim ćemo od duljine izvornih nizova oduzeti duljinu svakog niza vraćenog funkcijom SUBSTITUTE. Ovo će opet vratiti niz.
      • Sada će SUM dodati sve ove brojeve. Ovo je brojanje brojeva u nizu.

Izdvoji dio broja iz niza

Pošto znamo duljinu brojeva u niz, ovu ćemo funkciju zamijeniti LIJEVO.
Budući da imamo naš niz A11 naš:

Formula za izdvajanje brojeva iz LIJEVO

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


Izdvoji dio teksta iz niza

Budući da znamo broj brojeva, možemo ga oduzeti od ukupne duljine niza da bismo dobili nizove abecede u nizu, a zatim pomoću desne funkcije izvući taj broj znakova s ​​desne strane niza.

Formula za izdvajanje teksta iz DESNO

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


Kako radi
Glavni dio obje formule je SUM (LEN (A11) -LEN (ZAMJENA (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) koji izračunava prvu pojavu broja. Tek nakon što to pronađemo, možemo podijeliti tekst i broj pomoću funkcije LIJEVO. Pa shvatimo ovo.

      • ZAMJENA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "")): Ovaj dio vraća niz nizova u A11 nakon zamjene ovih brojeva s ništa/prazno (“”). Za 27Monta vratit će se {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
      • LEN (ZAMJENA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Sada je dio ZAMJENA omotan funkcijom LEN. Ova povratna duljina tekstova u nizu koju vraća funkcija SUBSTITUTE. Kao rezultat toga imat ćemo {7,7,6,7,7,7,7,6,7,7}.
      • LEN (A11) -LEN (ZAMJENA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," "))): Ovdje oduzimamo svaki broj vraćen gornjim dijelom od duljine stvarnog niza. Dužina izvornog teksta je 7. Stoga ćemo imati {7-7,7-7,7-6,….}. Konačno ćemo imati {0,0,1,0,0,0,0,1,0,0}.
      • ZBIR (LEN (A11) -LEN (ZAMJENA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Ovdje smo koristili SUM za zbrajanje niza vraćenog gornjim dijelom funkcije. To će dati 2. Što je broj brojeva u nizu.

Sada pomoću ovoga možemo izdvojiti tekstove i broj i podijeliti ih u različite ćelije. Ova metoda će raditi s obje vrste teksta, kada je broj na početku i kada je na kraju. Vi samo trebate prikladno koristiti LIJEVU i DESNU funkciju.
Za razdvajanje brojeva i tekstova iz niza koristite funkciju SplitNumText
Gore navedene metode su malo složene i nisu korisne kada se tekst i brojevi pomiješaju. Za podjelu teksta i brojeva koristite ovu korisnički definiranu funkciju.

Sintaksa:

= SplitNumText (niz, op)

Niz: Niz koji želite podijeliti.
Op: ovo je boolean. Proći 0 ili lažno da biste dobili dio teksta. Za brojčani dio, proslijedite pravi ili bilo koji broj veći od 0.
Na primjer, ako je niz u A20 tada,
Formula za izdvajanje brojeva iz niza je:

= SplitNumText (A20,1)

I
Formula za izdvajanje teksta iz niza je:

= SplitNumText (A20,0)

Kopirajte donji kod u VBA modul kako bi gornja formula funkcionirala.

Funkcija SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) then num = num & Mid (str, i , 1) Ostalo txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Funkcija

Ovaj kôd jednostavno provjerava svaki znak u nizu, ako je njegov broj ili ne. Ako je to broj, tada se sprema u num varijablu else u txt varijablu. Ako korisnik prenese true za op tada se vraća num inače se vraća txt.

Ovo je najbolji način za razdvajanje broja i teksta iz niza po mom mišljenju.
Radnu knjigu možete preuzeti ovdje ako želite.

Pa da, dečki, ovo su načini dijeljenja teksta i brojeva u različite ćelije. Javite mi ako imate bilo kakvih nedoumica ili boljeg rješenja u donjem odjeljku komentara. Uvijek je zabavno komunicirati s dečkima.

Kliknite donju vezu za preuzimanje radne datoteke:

Podijelite broj i tekst iz ćelije

Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti
Funkcija VLOOKUP u Excelu
COUNTIF u Excelu 2016
Kako koristiti funkciju SUMIF u Excelu