Přejít k hlavnímu obsahu

Odstraňte nebo odeberte nečíselné znaky z textových řetězců

Někdy možná budete muset odstranit všechny nečíselné znaky z textových řetězců a ponechat pouze čísla uvedená níže. Tento článek představí některé vzorce pro řešení tohoto úkolu v aplikaci Excel.


Odstraňte nebo odeberte všechny nečíselné znaky z textových řetězců pomocí vzorců

V aplikaci Excel 2019 a Office 365 vám nová funkce TEXTJOIN kombinující s funkcemi IFERROR, MID, ROW a INDIRECT pomůže extrahovat pouze čísla z textového řetězce, obecná syntaxe je:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text: textový řetězec nebo hodnota buňky, ze které chcete odebrat všechny nečíselné znaky.

1. Zkopírujte nebo zadejte následující vzorec do prázdné buňky, do které chcete odeslat výsledek:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2. A pak stiskněte Ctrl + Shift + Enter společně získáte první výsledek, viz screenshot:

3. Poté vyberte buňku vzorce a potom přetáhněte popisovač výplně dolů do buněk, na které chcete použít tento vzorec, byla extrahována pouze čísla a byly odstraněny všechny ostatní nečíselné znaky, viz screenshot:


Vysvětlení vzorce:

ŘÁDEK (NEPŘÍMÝ ("1: 100"): Číslo 1: 100 ve vzorci NEPŘÍMÉ znamená, že funkce MID vyhodnotí 100 znaků textového řetězce. Toto pole bude obsahovat 100 čísel, jako je tato: {1; 2; 3; 4; 5; 6; 7; 8 ... 98; 99; 100}.
Poznámka: Pokud je váš textový řetězec mnohem delší, můžete podle potřeby změnit číslo 100 na větší číslo.

MID (A2, ROW (INDIRECT ("1: 100")), 1: Tato funkce MID se používá k extrakci textu v buňce A2 za účelem získání jednoho znaku a vytvoří pole jako toto:
{"5"; "0"; "0"; ""; "K"; "u"; "t"; "o"; "o"; "l"; "s"; ""; "f" ; "o"; "r"; ""; "E"; "x"; "c"; "e"; "l"; ""; ""; ""; ""; ""; "". ..}

MID(A2,ROW(INDIRECT("1:100")),1)+0: Přidání hodnoty 0 poté, co se toto pole použije k vynucení textu na číslo, bude číselná textová hodnota převedena na číslo a nečíselné hodnoty budou zobrazeny jako chybová hodnota #HODNOTA, jako je tato:
{"5"; "0"; "0"; # HODNOTA!; # HODNOTA!; # HODNOTA!; # HODNOTA!; # HODNOTA!; # HODNOTA! !;#HODNOTA! !;#HODNOTA!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: Tato funkce IFERROR slouží k nahrazení všech chybových hodnot prázdným řetězcem, jako je tento:
{"5"; "0"; "0"; ""; ""; ""; ""; ""; ""; ""; ""; …}

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): Nakonec bude tato funkce TEXTJION kombinovat všechny neprázdné hodnoty v poli, které vrátí funkce IFFERROR, a vrátí výsledek.


Poznámky:

1. S výše uvedeným vzorcem budou čísla vrácena jako textový formát, pokud potřebujete skutečnou číselnou hodnotu, použijte tento vzorec, nezapomeňte stisknout Ctrl + Shift + Enter dohromady, abyste dosáhli správného výsledku.

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2. V dřívějších verzích aplikace Excel tento vzorec nebude fungovat, v tomto případě vám může pomoci následující vzorec, zkopírujte nebo zadejte tento vzorec do prázdné buňky:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)


Oddělte nebo odeberte všechny nečíselné znaky z textových řetězců pomocí snadné funkce

Možná je příliš dlouhé si pamatovat výše uvedené vzorce, zde představím Kutools pro Excel pro vás, s jeho Odebrat znaky Tato funkce umožňuje odstranit číselné, abecední, netisknutelné nebo alfanumerické znaky z textových řetězců pouhými několika kliknutími. Klikněte a stáhněte si zdarma Kutools pro Excel!


Použité relativní funkce:

  • TEXTJOIN:
  • Funkce TEXTJOIN spojuje více hodnot z řádku, sloupce nebo oblasti buněk se specifickým oddělovačem.
  • MID:
  • Funkce MID se používá k vyhledání a vrácení konkrétního počtu znaků ze středu zadaného textového řetězce.
  • ROW:
  • Funkce Excel ROW vrací číslo řádku odkazu.
  • INDIRECT:
  • Funkce Excel INDIRECT převádí textový řetězec na platný odkaz.
  • IFERROR:
  • Funkce IFERROR se používá k vrácení vlastního výsledku, když vzorec vyhodnotí chybu, a vrátí normální výsledek, pokud nedojde k žádné chybě.

Další články:


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What is this formula for french settings?
This comment was minimized by the moderator on the site
Thanks for this. Nice formula.How would I alter it so that if the cell contains only letters the formula enters a 0 the results cell (rather than just blank as it is at the moment)?Thought I might be able to do it by wrapping the formula in another IF statement but I’m not getting very far.
This comment was minimized by the moderator on the site
Hello, Glenn,To display the results as blanks rather than zeros, please apply the following formula:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Wow that’ll take some digesting 😋 Thanks for taking the time to reply 👍
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations