Jak rozdělit čísla a text z řetězce v aplikaci Excel

Anonim

Mnohokrát dostávám k analýze smíšená data z pole a serveru. Tato data jsou obvykle špinavá a obsahují sloupec s čísly a textem. Při čištění dat před analýzou odděluji čísla a text v samostatných sloupcích. V tomto článku vám řeknu, jak to můžete udělat.

Scénář:
Takže jeden náš přítel na Exceltip.com položil tuto otázku v sekci komentáře. "Jak oddělím čísla před textem a na konci textu pomocí Excelu Formula." Například 125EvenueStreet a LoveYou3000 atd. “

K extrahování textu používáme PRAVÉ, LEVÉ, MID a další textové funkce. Potřebujeme znát počet textů, které je třeba extrahovat. A tady uděláme to samé jako první.
Extrahujte číslo a text z řetězce, když je číslo na konci řetězce
Pro výše uvedený příklad jsem připravil tento list. V buňce A2 mám řetězec. V buňce B2 chci textovou část a v C2 číselnou část.

Potřebujeme tedy znát pozici, odkud číslo začíná. Poté použijeme funkci Left a další. Abychom získali pozici prvního čísla, použijeme níže obecný vzorec:
Obecný vzorec pro získání pozice prvního čísla v řetězci:

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

Tím se vrátí pozice prvního čísla.
Pro výše uvedený příklad napište tento vzorec do libovolné buňky.

= MIN (HLEDÁNÍ ({0,1,2,3,4,5,6,7,8,9}, A5 a "0123456789"))

Extrahovat část textu

Vrátí 15, protože první nalezené číslo je na 15. pozici v textu. Vysvětlím to později.

Nyní, abychom získali text, musíme zleva získat 15-1 znak z řetězce. Takže použijeme
Funkce VLEVO pro extrahování textu.
Vzorec pro extrahování textu zleva

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


Zde jsme právě odečetli 1 od jakéhokoli čísla vráceného MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Extrahovat číslo části

Nyní k získání čísel stačí získat číselné znaky od 1. nalezeného čísla. Vypočítáme tedy celkovou délku tětiva a odečtěte pozici prvního nalezeného čísla a přidejte 1 k tomu. Jednoduchý. Ano, zní to jednoduše a jednoduše.
Vzorec pro extrahování čísel zprava

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

Zde jsme pomocí funkce LEN získali celkovou délku řetězce a poté odečetli pozici prvního nalezeného čísla a poté k němu přidali 1. To nám dává celkový počet čísel. Zde se dozvíte více o extrahování textu pomocí funkcí VLEVO a VPRAVO v Excelu.

Funkční část VLEVO a VPRAVO je tedy jednoduchá. Tricky část je MIN a SEARCH část, která nám dává pozici prvního nalezeného čísla. Pojďme to pochopit.
Jak to funguje
Víme, jak funguje funkce DOLEVA a DOPRAVA. Prozkoumáme hlavní část tohoto vzorce, která získá pozici prvního nalezeného čísla, a to je: MIN (HLEDAT ({0,1,2,3,4,5,6,7,8,9}, řetězec & "0123456789 ")
Funkce SEARCH vrací pozici textu v řetězci. Funkce SEARCH („text“, „řetězec“) má dva argumenty, nejprve text, který chcete prohledávat, a druhý řetězec, ve kterém chcete hledat.

    • Zde v SEARCH máme na pozici textu pole čísel od 0 do 9. A na pozici řetězce máme řetězec, který je zřetězen s „0123456789“ pomocí & operátor. Proč? Řeknu ti to.
    • Každý prvek v poli {0,1,2,3,4,5,6,7,8,9} bude prohledán v daném řetězci a vrátí svoji pozici v řetězci formuláře pole na stejném indexu v poli.
    • Pokud není nalezena žádná hodnota, způsobí to chybu. Veškerý vzorec tedy vyústí v chybu. Abychom tomu zabránili, spojili jsme v textu čísla „0123456789“. Takže vždy najde každé číslo v řetězci. Tato čísla jsou nakonec tedy nezpůsobí žádný problém.
    • Funkce MIN nyní vrací nejmenší hodnotu z pole vráceného funkcí SEARCH. Tato nejmenší hodnota bude prvním číslem v řetězci. Nyní pomocí této funkce ČÍSLO a DOLEVA a DOPRAVA můžeme rozdělit části textu a řetězce.

Podívejme se na náš příklad. V A5 máme řetězec, který má název ulice a číslo domu. Musíme je rozdělit do různých buněk.
Nejprve se podívejme, jak jsme získali pozici prvního čísla v řetězci.

    • MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): toto se převede na MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9}, “Monta270123456789”))

Nyní, jak jsem vysvětlil, vyhledávání prohledá každé číslo v poli {0,1,2,3,4,5,6,7,8,9} v Monta270123456789 a vrátí svou pozici ve formě pole. Vrácené pole bude {8,9,6,11,12,13,14,7,16,17}. Jak?
0 bude vyhledáno v řetězci. Nachází se v poloze 8. Náš první prvek je tedy 8. Všimněte si, že náš původní text je dlouhý pouze 7 znaků. Pochopit to. 0 není součástí 27. Monta
Další 1 bude prohledána v řetězci a také není součástí původního řetězce a dostaneme její pozici 9.
Budou prohledávány další 2. Protože je součástí původního řetězce, získáme jeho index jako 6.
Podobně se každý prvek nachází na nějaké pozici.

    • Nyní je toto pole předáno funkci MIN jako MIN ({8,9,6,11,12,13,14,7,16,17}). MIN vrací 6, což je pozice prvního čísla nalezeného v původním textu.
      A příběh po tom je docela jednoduchý. Toto číslo používáme k extrahování textu a čísel pomocí funkce VLEVO a VPRAVO.

Extrahujte číslo a text z řetězce, když je číslo na začátku řetězce
Ve výše uvedeném příkladu bylo Number na konci řetězce. Jak extrahujeme číslo a text, když je číslo na začátku.

Připravil jsem podobnou tabulku jako výše. Na začátku to má jen číslo.

Zde použijeme jinou techniku. Spočítáme délku čísel (což jsou 2 zde) a extrahujeme tento počet znaků z levé části řetězce.
Metoda je tedy = LEFT (řetězec, počet čísel)
Chcete -li spočítat počet znaků, jedná se o vzorec.
Obecný vzorec pro počítání počtu čísel:

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

Tady,

      • Funkce SUBSTITUTE nahradí každé nalezené číslo „“ (prázdné). Pokud je nalezeno číslo nahrazeno a do pole bude přidán nový řetězec, do pole bude přidán jiný rozumný původní řetězec. Tímto způsobem budeme mít pole 10 řetězců.
      • Funkce LEN nyní vrátí délku znaků v poli těchto řetězců.
      • Potom z délky původních řetězců odečteme délku každého řetězce vráceného funkcí SUBSTITUTE. Tím se opět vrátí pole.
      • Nyní SUM přidá všechna tato čísla. Toto je počet čísel v řetězci.

Extrahujte část čísla ze řetězce

Nyní, když známe délku čísel v tětiva, tuto funkci nahradíme VLEVO.
Protože máme řetězec A11, naše:

Vzorec pro extrahování čísel ZLEVA

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


Extrahujte textovou část z řetězce

Protože známe počet čísel, můžeme jej odečíst od celkové délky řetězce, abychom získali číselné abecedy v řetězci, a poté pomocí pravé funkce extrahovat tento počet znaků zprava od řetězce.

Vzorec pro extrahování textu zprava

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


Jak to funguje
Hlavní část v obou vzorcích je SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," ")))), která vypočítá první výskyt čísla. Teprve po zjištění tohoto jsme schopni rozdělit text a číslo pomocí funkce VLEVO. Pojďme to tedy pochopit.

      • NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Tato část vrací pole řetězce v A11 po nahrazení těchto čísel ničím/prázdným („“). Pro 27 Monta vrátí {„27Monta“, „27Monta“, „7Monta“, „27Monta“, „27Monta“, „27Monta“, „27Monta“, „2Monta“, „27Monta“, „27Monta“}.
      • LEN (NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Nyní je část SUBSTITUTE zabalena funkcí LEN. Tato návratová délka textů v poli vrácená funkcí SUBSTITUTE. V důsledku toho budeme mít {7,7,6,7,7,7,7,6,6,7,7}.
      • LEN (A11) -LEN (NÁHRADA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," "))): Zde odečteme každé číslo vrácené výše uvedenou částí od délky skutečného řetězce. Délka původního textu je 7. Proto budeme mít {7-7,7-7,7-6, ….}. Nakonec budeme mít {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"}, ""))): Zde jsme použili SUM k sečtení pole vráceného výše uvedenou částí funkce. To dá 2. Což je počet čísel v řetězci.

Nyní pomocí toho můžeme extrahovat texty a čísla a rozdělit je do různých buněk. Tato metoda bude fungovat s textem typu, když je číslo na začátku a když je na konci. Stačí vhodně využívat levou a pravou funkci.
K rozdělení čísel a textů z řetězce použijte funkci SplitNumText
Výše uvedené metody jsou trochu složité a nejsou užitečné při smísení textu a čísel. K rozdělení textu a čísel použijte tuto uživatelem definovanou funkci.

Syntax:

= SplitNumText (řetězec, op)

Tětiva: Řetězec, který chcete rozdělit.
Operace: to je booleovské. Projděte 0 nebo Nepravdivé získat textovou část. Pro číselnou část projděte skutečný nebo jakékoli číslo větší než 0.
Pokud je například řetězec v A20, pak,
Vzorec pro extrahování čísel z řetězce je:

= SplitNumText (A20,1)

A
Vzorec pro extrahování textu z řetězce je:

= SplitNumText (A20,0)

Zkopírujte níže uvedený kód do modulu VBA, aby výše uvedený vzorec fungoval.

Funkce 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) Else txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function

Tento kód jednoduše zkontroluje každý znak v řetězci, ať už je to číslo nebo ne. Pokud se jedná o číslo, je uloženo v proměnné num else v proměnné txt. Pokud uživatel pro operaci předá true, vrátí se num, jinak se vrátí txt.

To je podle mě nejlepší způsob, jak rozdělit číslo a text z řetězce.
Pokud chcete, sešit si můžete stáhnout zde.

Takže jo, lidi, toto jsou způsoby, jak rozdělit text a čísla do různých buněk. Dejte mi vědět, pokud máte nějaké pochybnosti nebo nějaké lepší řešení v sekci komentáře níže. Komunikace s kluky je vždy zábava.

Pracovní soubor stáhnete kliknutím na odkaz níže:

Rozdělit číslo a text z buňky

Oblíbené články:
50 zkratek aplikace Excel ke zvýšení produktivity
Funkce VLOOKUP v Excelu
COUNTIF v aplikaci Excel 2016
Jak používat funkci SUMIF v aplikaci Excel