Ako rozdeliť čísla a text z reťazca v programe Excel

Anonim

Mnohokrát dostanem na analýzu zmiešané údaje z poľa a servera. Tieto údaje sú zvyčajne špinavé, pretože majú stĺpec zmiešaný s číslami a textom. Pri čistení údajov pred analýzou oddeľujem čísla a text do samostatných stĺpcov. V tomto článku vám poviem, ako to môžete urobiť.

Scenár:
Takže jeden náš priateľ na Exceltip.com položil túto otázku v sekcii komentárov. „Ako oddelím čísla nachádzajúce sa pred textom a na konci textu pomocou programu Excel Formula. Napríklad 125EvenueStreet a LoveYou3000 atď. “

Na extrahovanie textu používame PRAVÉ, ĽAVÉ, MIDI a ďalšie textové funkcie. Potrebujeme iba poznať počet textov, ktoré je potrebné extrahovať. A tu najskôr urobíme to isté.
Extrahujte číslo a text z reťazca, ak je číslo na konci reťazca
Pre vyššie uvedený príklad som pripravil tento list. V bunke A2 mám reťazec. V bunke B2 chcem textovú časť a v C2 číselnú časť.

Potrebujeme teda poznať pozíciu, od ktorej číslo začína. Potom použijeme funkciu Vľavo a inú. Aby sme získali pozíciu prvého čísla, použijeme nižšie uvedený generický vzorec:
Všeobecný vzorec na získanie pozície prvého čísla v reťazci:

= MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Tým sa vráti pozícia prvého čísla.
Pre vyššie uvedený príklad napíšte tento vzorec do ľubovoľnej bunky.

= MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, A5 a "0123456789"))

Extrahovať časť textu

Vráti 15, pretože prvé nájdené číslo je v texte na 15. mieste. Vysvetlím to neskôr.

Teraz, aby sme získali text, zľava potrebujeme získať 15-1 znak z reťazca. Takže použijeme
Ľavá funkcia na extrahovanie textu.
Vzorec na extrahovanie textu zľava

= VĽAVO (A5, MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, A5 a "0123456789"))-1)


Tu sme iba odpočítali 1 od akéhokoľvek čísla vráteného MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, A5 a "0123456789")).
Extrahovať číslo časti

Teraz, aby sme získali čísla, stačí získať číselné znaky z prvého nájdeného čísla. Vypočítame teda celkovú dĺžku reťazec a odpočítajte polohu prvého nájdeného čísla a pridajte 1 k tomu. Jednoduché. Áno, znie to jednoducho zložito, je to jednoduché.
Vzorec na extrahovanie čísel sprava

= SPRÁVNE (A5, LEN (A5) -MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))+1)

Tu sme len získali celkovú dĺžku reťazca pomocou funkcie LEN a potom sme odpočítali pozíciu prvého nájdeného čísla a potom sme k nemu pridali 1. To nám dáva celkový počet čísel. Tu sa dozviete viac o extrahovaní textu pomocou funkcií VĽAVO a VPRAVO v programe Excel.

Funkčná časť VĽAVO a VPRAVO je teda jednoduchá. Tricky časť je MIN a SEARCH časť, ktorá nám dáva pozíciu prvého nájdeného čísla. Poďme to pochopiť.
Ako to funguje
Vieme, ako funguje funkcia ĽAVÁ a PRAVÁ. Preskúmame hlavnú časť tohto vzorca, ktorá získa pozíciu prvého nájdeného čísla, a to je: MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, reťazec & "0123456789 ")
Funkcia SEARCH vracia pozíciu textu v reťazci. Funkcia SEARCH („text“, „reťazec“) obsahuje dva argumenty, najskôr text, ktorý chcete hľadať, a druhý reťazec, v ktorom chcete hľadať.

    • Tu v VYHĽADÁVANÍ, na pozícii textu máme pole čísel od 0 do 9. A na pozícii reťazca máme reťazec, ktorý je spojený s „0123456789“ pomocou & operátor. Prečo? Poviem ti.
    • Každý prvok v poli {0,1,2,3,4,5,6,7,8,9} bude vyhľadaný v danom reťazci a vráti svoju pozíciu v poli vo forme reťazca s rovnakým indexom v poli.
    • Ak sa nenájde žiadna hodnota, spôsobí to chybu. Preto celý vzorec bude mať za následok chybu. Aby sme tomu zabránili, zreťazili sme v texte čísla „0123456789“. Aby vždy našiel každé číslo v reťazci. Tieto čísla sú nakoniec, a preto nespôsobia žiadny problém.
    • Teraz funkcia MIN vráti najmenšiu hodnotu z poľa vráteného funkciou SEARCH. Táto najmenšia hodnota bude prvým číslom v reťazci. Teraz pomocou tejto funkcie NUMBER a LEFT a RIGHT môžeme rozdeliť časti textu a reťazca.

Pozrime sa na náš príklad. V A5 máme reťazec, ktorý má názov ulice a číslo domu. Musíme ich rozdeliť do rôznych buniek.
Najprv sa pozrime, ako sme získali pozíciu prvého čísla v reťazci.

    • MIN (VYHĽADÁVANIE ({0,1,2,3,4,5,6,7,8,9}, A5 a "0123456789")): toto sa prekladá do MIN (VYHĽADÁVANIE ({0,1,2,3, 4,5,6,7,8,9}, “Monta270123456789”))

Teraz, ako som vysvetlil, vyhľadávanie vyhľadá každé číslo v poli {0,1,2,3,4,5,6,7,8,9} v Monta270123456789 a vráti svoju pozíciu vo forme poľa. Vrátené pole bude {8,9,6,11,12,13,14,7,16,17}. Ako?
0 sa bude hľadať v reťazci. Nachádza sa v polohe 8. Náš prvý prvok je preto 8. Všimnite si, že náš pôvodný text má iba 7 znakov. Získajte to. 0 nie je súčasťou Monta27.
Nasledujúca 1 sa vyhľadá v reťazci a tiež nie je súčasťou pôvodného reťazca, a získame pozíciu 9.
Budú sa hľadať ďalšie 2. Pretože je súčasťou pôvodného reťazca, dostaneme jeho index ako 6.
Podobne sa každý prvok nachádza v určitej polohe.

    • Teraz je toto pole odovzdané do funkcie MIN ako MIN ({8,9,6,11,12,13,14,7,16,17}). MIN vráti 6, čo je pozícia prvého čísla v pôvodnom texte.
      A príbeh po tomto je celkom jednoduchý. Toto číslo používame na extrahovanie textu a čísel pomocou funkcie VLEVO a VPRAVO.

Extrahujte číslo a text z reťazca, keď je číslo na začiatku reťazca
V uvedenom príklade bolo Number na konci reťazca. Ako extrahujeme číslo a text, keď je číslo na začiatku.

Pripravil som podobnú tabuľku ako vyššie. Na začiatku to má iba číslo.

Tu použijeme inú techniku. Spočítame dĺžku čísel (čo sú tu 2) a tento počet znakov extrahujeme z ľavej časti reťazca.
Metóda je teda = LEFT (reťazec, počet čísel)
Na spočítanie počtu znakov je to vzorec.
Generický vzorec na spočítanie počtu čísel:

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

Tu,

      • Funkcia SUBSTITUTE nahradí každé nájdené číslo znakom „“ (prázdne). Ak sa nájde číslo nahradené a do poľa sa pridá nový reťazec, do poľa sa pridá iný pôvodný reťazec. Týmto spôsobom budeme mať pole 10 reťazcov.
      • Teraz funkcia LEN vráti dĺžku znakov v poli týchto reťazcov.
      • Potom z dĺžky pôvodných reťazcov odpočítame dĺžku každého reťazca vráteného funkciou SUBSTITUTE. Tým sa opäť vráti pole.
      • Teraz SUM pridá všetky tieto čísla. Toto je počet čísel v reťazci.

Extrahujte časť čísla z reťazca

Teraz, keď poznáme dĺžku čísel v reťazec, nahradíme túto funkciu v LEFT.
Pretože máme reťazec A11, naše:

Vzorec na extrahovanie čísel ZĽAVA

= Vľavo , "8", "9"}, "")))))


Extrahujte textovú časť z reťazca

Pretože poznáme počet čísel, môžeme ho odpočítať od celkovej dĺžky reťazca, aby sme získali číselné abecedy v reťazci, a potom pomocou pravej funkcie extrahovať tento počet znakov sprava od reťazca.

Vzorec na extrahovanie textu sprava

= PRAVÉ (A11, LEN (A2) -SUM (LEN (A11) -LEN (NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6 "," 7 "," 8 "," 9 "}," ")))))


Ako to funguje
Hlavnou súčasťou obidvoch vzorcov je SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," ")))), ktorá vypočíta prvý výskyt čísla. Až keď to zistíme, sme schopní rozdeliť text a číslo pomocou funkcie VĽAVO. Poďme to teda pochopiť.

      • NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Táto časť vracia pole reťazcov v A11 po náhrade týchto čísel za nič/prázdne („“). Pre 27 Monte vráti {{27Monta "," 27Monta "," 7Monta "," 27Monta "," 27Monta "," 27Monta "," 27Monta "," 2Monta "," 27Monta "," 27Monta "}.
      • LEN (NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Teraz je časť NÁHRADY zalomená funkciou LEN. Táto návratová dĺžka textov v poli vrátená funkciou SUBSTITUTE. V dôsledku toho budeme mať {7,7,6,7,7,7,7,7,6,7,7}.
      • LEN (A11) -LEN (NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," "))): Tu odpočítame každé číslo vrátené vyššie uvedenou časťou od dĺžky skutočného reťazca. Dĺžka pôvodného textu je 7. Preto budeme mať {7-7,7-7,7-6, ….}. Nakoniec budeme mať {0,0,1,0,0,0,0,0,1,0,0}.
      • SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Tu sme použili SUM na súčet poľa vráteného vyššie uvedenou časťou funkcie. Výsledkom bude 2. Čo je počet čísel v reťazci.

Teraz pomocou toho môžeme extrahovať texty a čísla a rozdeľovať ich do rôznych buniek. Táto metóda bude fungovať s textom typu, keď je číslo na začiatku a keď je na konci. Musíte len vhodne využiť funkciu VĽAVO a VPRAVO.
Na rozdelenie čísel a textov z reťazca použite funkciu SplitNumText
Vyššie uvedené metódy sú trochu zložité a nie sú užitočné, keď sú text a čísla zmiešané. Na rozdelenie textu a čísel použite túto funkciu definovanú používateľom.

Syntax:

= SplitNumText (reťazec, op)

Reťazec: Reťazec, ktorý chcete rozdeliť.
Operácia: toto je boolean. Prejdite 0 alebo falošný získať textovú časť. Pokiaľ ide o číselnú časť, prejdite pravda alebo akékoľvek číslo väčšie ako 0.
Ak je napríklad reťazec v A20, potom,
Vzorec na extrahovanie čísel z reťazca je:

= SplitNumText (A20,1)

A
Vzorec na extrahovanie textu z reťazca je:

= SplitNumText (A20,0)

Skopírujte nižšie uvedený kód do modulu VBA, aby vyššie uvedený vzorec fungoval.

Funkcia 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) Inak txt = txt & stred (str, i, 1) Koniec, ak bude ďalší i Ak op = True, potom SplitNumText = počet ostatných znakov SplitNumText = txt Koniec, ak funkcia ukončenia

Tento kód jednoducho skontroluje každý znak v reťazci, či je číslo alebo nie. Ak je to číslo, potom je uložené v premennej num else v premennej txt. Ak používateľ zadá hodnotu true pre operáciu, vráti sa num, inak sa vráti txt.

Podľa mňa je to najlepší spôsob, ako rozdeliť číslo a text z reťazca.
Pracovný zošit si môžete stiahnuť tu, ak chcete.

Takže áno, chlapci, toto sú spôsoby, ako rozdeliť text a čísla do rôznych buniek. Dajte mi vedieť, ak máte akékoľvek pochybnosti alebo lepšie riešenie v nižšie uvedenej sekcii komentárov. Komunikácia s chlapcami je vždy zábavná.

Kliknutím na odkaz nižšie si stiahnete pracovný súbor:

Rozdeľte číslo a text z bunky

Populárne články:
50 skratiek programu Excel na zvýšenie produktivity
Funkcia VLOOKUP v programe Excel
COUNTIF v Exceli 2016
Ako používať funkciu SUMIF v programe Excel