Excel tutoriál – extrahujte text nebo číslo podle konkrétní pozice

V mnoha případech možná budete potřebovat pouze extrahovat užitečný obsah z věty nebo textového řetězce v buňce, například extrahovat provincii z adresy, extrahovat e-mailovou adresu z věty, extrahovat číslo kurýrního účtu z konverzace, a tak dále. Tento kurz zužuje extrakci na konkrétní pozici v buňce a shromažďuje různé metody, které vám pomohou extrahovat text nebo čísla z buňky podle konkrétní pozice v Excelu.

---

Extrahujte text podle pozice

Tato část shromažďuje běžné pozice, kde lze text extrahovat z buňky, a poskytuje odpovídající metody, jak je krok za krokem zpracovat. Další podrobnosti můžete procházet.

1. Extrahujte počet znaků zleva nebo zprava

Chcete-li extrahovat počet znaků z levé nebo pravé strany řetězce, můžete zkusit jednu z níže uvedených metod.

1.1 Extrahujte prvních nebo posledních N znaků pomocí vzorců

Předpokládejme, že máte ve sloupci B seznam textových řetězců, jak je znázorněno na obrázku níže, k extrahování prvních 2 znaků a posledních 2 znaků z každého řetězce můžete použít následující vzorce.

Extrahujte prvních N znaků z textového řetězce

Funkce VLEVO může pomoci snadno extrahovat prvních N znaků z textového řetězce v Excelu.

Obecný vzorec

=LEFT(text_string,[num_chars])

Argumenty

Nyní můžete tento vzorec použít k extrahování prvních 2 znaků z buněk ve sloupci B.

1. Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte vstoupit klíč k získání prvního výsledku. Vyberte výslednou buňku a přetáhněte její úchyt automatického vyplňování dolů, abyste použili vzorec na další buňky.

=LEFT(B5,2)

Nyní byly extrahovány první 2 znaky v každé buňce rozsahu B5:B10.

Extrahujte posledních N znaků z textového řetězce

Zde použijeme funkci RIGHT k extrahování posledních N znaků z textového řetězce v Excelu.

Obecný vzorec

=RIGHT(text_string,[num_chars])

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Poté vyberte tuto buňku výsledku a přetáhněte její úchyt automatického vyplňování dolů, abyste získali další výsledky.

=RIGHT(B5,2)

1.2 Extrahujte prvních nebo posledních N znaků pomocí úžasného nástroje

I když jsou výše uvedené vzorce jednoduché, k extrahování prvních nebo posledních n znaků z dlouhého seznamu textového řetězce musíte stále přetahovat popisovač automatického vyplňování shora dolů, což může být trochu časově náročné. Zde doporučuje Kutools pro ExcelJe Extrahujte text nástroj, který vám pomůže hromadně extrahovat prvních nebo posledních N znaků ze seznamu textových řetězců.

1. Předem vyberte seznam textových řetězců, ze kterého chcete extrahovat text, a klepněte Kutools > Text > Extrahujte text.

2. Ve vyskakovacím okně Extrahujte text dialogové okno, musíte nakonfigurovat následujícím způsobem.

Poznámka: Chcete-li, aby byl výsledek dynamický při změně textového řetězce, můžete zaškrtnout Vložit jako vzorec box.

3. V dalším vyskakovacím okně Extrahujte text dialogovém okně vyberte buňku pro výstup extrahovaných znaků a poté klepněte OK.

Poté se z vybraných buněk hromadně extrahuje zadaných prvních nebo posledních N znaků.

Klepnutím získáte další informace o této funkci.

---

2. Extrahujte text před nebo za určitým znakem/slovem

Chcete-li extrahovat text před nebo za určitým znakem nebo slovem, různé scénáře v této části budou vyhovovat vašim potřebám.

2.1 Extrahujte text před nebo za prvním oddělovačem (znakem)

Jak je znázorněno na obrázku níže, k extrahování textu před nebo za prvním oddělovačem z každé buňky v rozsahu B4:B10 můžete použít jednu z níže uvedených metod.

2.1.1 Extrahujte text před prvním oddělovačem pomocí vzorce

Použití vzorce založeného na funkcích LEFT a FIND může pomoci extrahovat text před prvním oddělovačem z buňky. Chcete-li to provést, postupujte podle níže uvedených kroků.

Obecný vzorec

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo do ní zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání prvního výsledku. Vyberte první buňku výsledku a přetáhněte její úchyt automatického vyplňování dolů, abyste dostali texty před první oddělovač ostatních buněk.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Extrahujte text za prvním oddělovačem pomocí vzorce

Níže uvedený vzorec pomáhá extrahovat text za prvním oddělovačem z buňky v aplikaci Excel.

Obecný vzorec

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

Argumenty

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Extrahujte text před nebo za prvním oddělovačem pomocí úžasného nástroje

Zde velmi doporučuje Extrahujte text užitečnost Kutools pro Excel. Pomocí této funkce můžete snadno hromadně extrahovat texty před nebo za prvním oddělovačem z řady buněk.

1. Vyberte oblast buněk, ze které chcete extrahovat text, a potom klepněte Kutools > Text > Extrahujte text.

2. V Extrahujte text dialogové okno, musíte nakonfigurovat následujícím způsobem.

Poznámka: Chcete-li, aby byl výsledek při změně textového řetězce dynamický, můžete zaškrtnout políčko Vložit jako vzorec.

3. Pak další Extrahujte text Zobrazí se dialogové okno, vyberte buňku pro výstup výsledků a klikněte OK.

Poté jsou z vybraných buněk najednou extrahovány texty před nebo za prvním oddělovačem.

Chcete-li se o této funkci dozvědět více, navštivte: Rychle extrahujte určitý text z buněk v aplikaci Excel.

2.2 Extrahujte text před nebo za posledním oddělovačem (znakem)

Ve výše uvedených krocích jsme se naučili metody extrahování textu před nebo za prvním oddělovačem z buňky. Jak je znázorněno na obrázku níže, tato část vám ukáže dva vzorce pro extrahování textu před nebo za posledním oddělovačem z buňky. Chcete-li to provést, postupujte podle níže uvedených kroků.

2.2.1 Extrahujte text před posledním oddělovačem pomocí vzorce

Chcete-li z buňky extrahovat text před posledním oddělovačem, můžete použít funkce SEARCH, LEN a SUBSTITUTE uvnitř funkce LEFT.

Obecný vzorec

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

Argumenty

Vyberte buňku, zadejte níže uvedený vzorec a stiskněte vstoupit klíč k získání výsledku. Vyberte tuto buňku výsledku a přetáhněte její úchyt automatického vyplňování dolů, abyste extrahovali texty z jiných textových řetězců ve stejném sloupci.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Extrahujte text za posledním oddělovačem pomocí vzorce

Po extrahování textu před posledním oddělovačem z buňky můžete použít níže uvedený vzorec a extrahovat text za posledním oddělovačem, jak potřebujete.

Obecný vzorec

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Argumenty

Vyberte buňku, zadejte níže uvedený vzorec a stiskněte vstoupit klíč k získání výsledku. Vyberte tuto buňku výsledku a přetáhněte její úchyt automatického vyplňování dolů, abyste extrahovali texty z jiných textových řetězců ve stejném sloupci.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Extrahujte text za n-tým znakem

Podívejte se na příklad níže, je zde seznam textového řetězce v rozsahu B4:B10, pro extrahování textu za třetím znakem z každé buňky můžete použít vzorec založený na funkci MID a funkci LEN.

Obecný vzorec

=MID(text_string,nth_char+1,LEN(text_string))

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo do ní zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte tuto buňku výsledku a přetáhněte její úchyt automatického vyplňování dolů, abyste získali další výsledky.

=MID(B5,3+1,LEN(B5))

2.4 Extrahujte n-té slovo z textového řetězce

Předpokládejme, že máte seznam textového řetězce, jak je znázorněno na obrázku níže, a chcete z textového řetězce extrahovat pouze n-té slovo, tato část vám poskytuje tři způsoby, jak to udělat.

2.4.1 Extrahujte n-té slovo pomocí vzorce

Můžete kombinovat funkce TRIM, MID, SUBSTITUTE, REPT a LEN a extrahovat n-té slovo z textového řetězce v buňce.

Obecný vzorec

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Argumenty

V tomto případě rozsah B5:B10 obsahuje textové řetězce, D5:D10 obsahuje čísla představující n-té slovo, aplikujme tento vzorec na extrahování n-tého slova z textového řetězce.

Vyberte prázdnou buňku, zkopírujte nebo do ní zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání prvního výsledku. Vyberte tuto výsledkovou buňku a přetáhněte její popisovač automatického vyplňování dolů, abyste získali n-té slovo ostatních buněk.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Poznámka: N-té číslo do vzorce můžete zadat přímo následovně.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Extrahujte n-té slovo pomocí funkce definované uživatelem

Kromě výše uvedeného vzorce můžete také použít uživatelem definovanou funkci k extrahování n-tého slova z buňky v Excelu.

1. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul, a poté zkopírujte VBA níže do okna Kód.

Kód VBA: Extrahujte n-té slovo z textového řetězce v buňce

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. zmáčkni Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.

4. Vraťte se zpět do listu obsahujícího textové řetězce, ze kterých chcete extrahovat n-té slovo. Vyberte prázdnou buňku, zkopírujte nebo do ní zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání n-tého slova.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Poznámka: ve vzorci je D5 buňka, která obsahuje číslo představující n-té slovo. Případně můžete přímo nahradit odkaz na buňku číslem.

5. Vyberte výslednou buňku a přetáhněte její úchyt automatického vyplňování dolů, abyste extrahovali n-té slovo z textových řetězců jiných buněk.

2.4.3 Extrahujte n-té slovo pomocí úžasného nástroje

Pokud nechcete ručně použít vzorec nebo uživatelem definovanou funkci uvedenou výše, zde doporučuje Kutools pro Excel Extrahujte n-té slovo v buňce užitečnost. Pomocí této funkce můžete snadno extrahovat n-té slovo z textového řetězce v buňce pouze několika kliknutími.

1. Vyberte buňku, do které chcete umístit výsledek, a klepněte Kutools > Pomocník vzorců > Text > Výpis n-té slovo v buňce. Viz snímek obrazovky:

2. V Pomocník vzorců dialogové okno, musíte nakonfigurovat následujícím způsobem.

3. Poté je z textového řetězce v buňce B5 extrahováno n-té (druhé) slovo a současně můžete vidět vytvoření vzorce. Vyberte tuto výsledkovou buňku a přetáhněte její popisovač automatického vyplňování dolů, abyste získali n-té slovo z jiných textových řetězců.

Klepnutím získáte další informace o této funkci.

2.5 Extrahujte text před nebo za n-tým výskytem oddělovače

Předpokládejme, že máte seznam textových řetězců, jak je znázorněno na obrázku níže. Chcete-li extrahovat text před nebo za druhým výskytem mezery, poskytuje tato část dva vzorce, které vám to pomohou.

2.5.1 Extrahujte text před n-tým výskytem oddělovače

K extrahování textu před n-tým výskytem oddělovače z buňky v Excelu můžete použít funkci VLEVO spolu s funkcemi SUBSTITUTE a FIND.

Obecný vzorec

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Argumenty

Vyberte buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte tuto buňku s výsledkem a přetáhněte ji Rukojeť automatického vyplňování dolů, abyste získali další výsledky v seznamu.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Poznámka: Ve vzorci je B5 buňka obsahující textový řetězec, ze kterého chcete extrahovat text; „ “ zde představuje mezeru a číslo 2 představuje druhý výskyt mezery. Můžete je měnit podle svých potřeb.

2.5.2 Extrahujte text po n-tém výskytu oddělovače

Chcete-li extrahovat text po n-tém výskytu oddělovače, můžete použít funkci RIGHT s funkcemi SUBSTITUTE, LEN a FIND.

Obecný vzorec

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Argumenty

Nyní můžete pomocí tohoto vzorce extrahovat text po druhém výskytu mezery z každé buňky v rozsahu B5:B10 následovně.

Vyberte buňku, zadejte níže uvedený vzorec a stiskněte vstoupit klíč k získání výsledku. Chcete-li získat další výsledky, vyberte tuto buňku výsledku a přetáhněte ji dolů Rukojeť automatického vyplňování.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Extrahujte text před nebo po zalomení řádku

Předpokládejme, že máte ve sloupci B seznam objednávek a chcete z každé buňky extrahovat pouze část s datem a část s číslem produktu. K tomu můžete použít níže uvedené vzorce aplikace Excel.

2.6.1 Extrahujte text před zalomením prvního řádku pomocí vzorce

Jak můžete vidět na obrázku výše, část data se nachází před zalomením prvního řádku v buňce. Tato část předvede funkci VLEVO spolu s funkcí HLEDAT, která vám pomůže extrahovat text před zalomením prvního řádku v buňce.

Obecný vzorec

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo do ní zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Poté můžete vidět text před extrahováním prvního konce řádku v každé buňce v rozsahu B5:B8, jak je znázorněno na obrázku níže.

Poznámka: CHAR(10) ve vzorci představuje zalomení řádku v systému Windows.

2.6.2 Extrahujte text po posledním zalomení řádku pomocí vzorce

V předchozím kroku jsme mluvili o tom, jak extrahovat text před zalomením prvního řádku v buňce. A tato část vás provede extrahováním textu po posledním zalomení řádku v buňce s jiným vzorcem.

Obecný vzorec

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

Argumenty

Vyberte prázdnou buňku, zadejte vzorec níže a stiskněte vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili vzorec na další buňky.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Poté produkt č. část každé buňky v seznamu je extrahována, jak je znázorněno na snímku obrazovky výše.

Poznámka: CHAR(10) ve vzorci představuje zalomení řádku v systému Windows.

2.7 Extrahujte text před nebo za slovem

V předchozích dílech jsme se naučili extrahovat text před nebo za znak nebo oddělovač. Co byste měli udělat, abyste extrahovali text před nebo za celým slovem? Tato část představí tři metody, které vám pomohou tento úkol splnit.

2.7.1 Extrahujte text před určitým slovem pomocí vzorce

Následující vzorec vám pomůže extrahovat text před určitým slovem v buňce v Excelu.

Obecný vzorec

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Argumenty

Vyberte prázdnou buňku, zadejte vzorec níže a stiskněte vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

V tomto příkladu budeme extrahovat všechny texty před slovem „Excel“, takže slovo napíšeme přímo do vzorce a uzavřeme ho do uvozovek. Nebo můžete odkazovat na buňku, která obsahuje slovo „Excel“.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Poznámky:

2.7.2 Extrahujte text za určitým slovem pomocí vzorce

Chcete-li extrahovat text za určitým slovem, můžete to provést pomocí následujícího vzorce.

Obecný vzorec

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Argumenty

Vyberte buňku, zadejte níže uvedený vzorec a stiskněte vstoupit klíč k získání výsledku. Vyberte výslednou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Poté můžete vidět všechny texty po extrahování slova „Excel“ v každé buňce, jak je znázorněno na obrázku níže.

Poznámky:

2.7.3 Extrahujte text před nebo za určitým slovem pomocí úžasného nástroje

Pokud máte pocit, že použití vzorce může způsobit spoustu nepříjemností, zde důrazně doporučujeme Extrahujte text užitečnost Kutools pro Excel. Tato funkce pomáhá automatizovat úlohu extrakce v Excelu pomocí několika kliknutí.

1. cvaknutí Kutools > Text > Extrahujte text pro povolení této funkce.

2. V Extrahujte text dialogové okno, proveďte následující nastavení.

Poznámky: Pokud chcete vytvořit dynamické výsledky, zaškrtněte Vložit jako vzorec box. Poté se výsledky automaticky aktualizují, když se změní data v rozsahu.

3. Pak Extrahujte text Zobrazí se dialogové okno, musíte vybrat buňku pro výstup výsledku a poté kliknout na OK .

Poté jsou okamžitě extrahovány texty před nebo za určitým slovem v každé buňce ve vybraném rozsahu.

Poznámka: Tato funkce rozlišuje malá a velká písmena.

Klepnutím získáte další informace o této funkci.

---

3. Výpis mezi znaky/slova

Pokud chcete extrahovat text mezi určité znaky nebo slova, vyzkoušejte následující metody.

3.1 Extrahujte text mezi dva znaky

Chcete-li extrahovat text mezi dva znaky, které mohou být stejné nebo různé znaky. Tato část poskytuje několik metod a můžete si vybrat jednu z nich podle svých potřeb.

3.1.1 Extrahujte text mezi dva stejné znaky pomocí vzorce

Jak je znázorněno na obrázku níže, ve sloupci B je seznam textových řetězců a chcete-li z každé buňky v rozsahu extrahovat číselnou část mezi znaky „/“, může vám pomoci následující vzorec.

Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte buňku s výsledkem a poté ji přetáhněte Rukojeť automatického vyplňování dolů, abyste získali výsledky pro další buňky v seznamu.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Poté se z každé buňky v rozsahu extrahuje text mezi dvěma stejnými znaky „/“. Viz snímek obrazovky:

Poznámka:

3.1.2 Extrahujte text mezi dva různé znaky pomocí vzorce

Poté, co se naučíte, jak extrahovat text mezi dva stejné znaky v buňce, zde předvedeme vzorec pro extrahování textu mezi dva různé znaky. Jak je znázorněno na obrázku níže, chcete-li extrahovat pouze e-mailovou adresu mezi „<“ a „>“ z každé buňky ve sloupci B, můžete postupovat následovně.

Obecný vzorec

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Můžete vidět, že se extrahuje pouze text mezi určenými znaky, jak je znázorněno na snímku obrazovky výše.

3.1.3 Extrahujte text mezi dva znaky pomocí úžasného nástroje

Zde velmi doporučuje Extrahujte řetězce mezi zadaným textem vlastnost Kutools pro Excel které vám pomohou snadno extrahovat text mezi dva stejné nebo různé znaky v buňce v Excelu.

1. Vyberte prázdnou buňku k odeslání výsledku a poté klepněte na Kutools > Pomocník vzorců > Pomocník vzorců.

2. V Pomocník vzorců dialogové okno, proveďte následující nastavení.

3. Poté se extrahuje pouze text mezi „<“ a „>“ v buňce B5. Mezitím byl vytvořen vzorec, můžete vybrat tuto výsledkovou buňku a poté přetáhnout její úchyt automatického vyplňování dolů, abyste extrahovali texty z jiných buněk ve stejném seznamu.

Klepnutím získáte další informace o této funkci.

3.1.4 Extrahovat text mezi dva znaky (včetně znaků) podle pravidla

Pokud chcete po extrahování zachovat dva znaky, zkuste použít pravidlo ve funkci Extrahovat text aplikace Kutools pro Excel.

1. cvaknutí Kutools > Text > Extrahujte text.

2. V Extrahujte text dialogové okno, proveďte následující nastavení.

3. Další Extrahujte text Zobrazí se dialogové okno, vyberte buňku pro výstup výsledku a poté klepněte na OK .

Poté se text mezi určenými znaky (včetně znaků) hromadně extrahuje z každé buňky ve vybraném rozsahu.

3.2 Extrahujte text mezi dvěma slovy

Kromě extrahování textu mezi dvěma znaky může být nutné extrahovat text mezi dvěma slovy. Například extrahujte všechny textové řetězce mezi dvěma slovy „KTE“ a „feature“ z každé buňky ve sloupci B, jak je znázorněno na obrázku níže. Chcete-li to provést, můžete zkusit jednu z následujících metod.

3.2.1 Extrahujte text mezi dvěma slovy pomocí vzorce

K extrakci všech textových řetězců mezi dvěma slovy v buňce můžete použít vzorec založený na funkci MID a funkci SEARCH.

Obecný vzorec

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Poznámka: Ve vzorci číslo 3 představuje délku znaku slova "KTE"; číslo 4 představuje délku znaku slova "KTE" plus 1.

Můžete vidět, že všechny textové řetězce mezi zadanými dvěma slovy jsou extrahovány z každé buňky ve sloupci B.

3.2.2 Extrahujte text mezi dvěma slovy pomocí úžasného nástroje

Pro mnoho uživatelů aplikace Excel může být obtížné si vzorce zapamatovat a zpracovat. Zde s Extrahujte řetězce mezi zadaným textem vlastnost Kutools pro Excel, můžete snadno extrahovat text mezi dvěma slovy pomocí několika kliknutí.

1. Vyberte buňku pro výstup výsledku a potom klepněte Kutools > Pomocník vzorců > Pomocník vzorců.

2. V Pomocník vzorců dialogové okno, musíte nakonfigurovat následujícím způsobem.

3. Poté jsou extrahovány všechny textové řetězce mezi dvěma slovy „KTE“ a „feature“ v buňce B5. Mezitím byl vytvořen vzorec, můžete vybrat tuto výslednou buňku a poté přetáhnout její úchyt automatického vyplňování dolů, abyste extrahovali texty z jiných buněk ve stejném seznamu.

---

Extrahujte čísla podle pozice

U seznamu alfanumerických řetězců mohou nastat tři případy:

1. Číslo je na začátku textu;
2. Číslo je na konci textu;
3. Číslo může být kdekoli v textu.

V této části poskytneme různé metody, které lze použít k extrakci čísel v každém z výše uvedených případů.

1 Vyjměte číslo z levé části řetězce

Tato část představí vzorec, který vám pomůže extrahovat pouze čísla, která se objevují před textem v buňce.

Obecný vzorec

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

Argumenty

Poznámka:

Vyberte prázdnou buňku, zadejte níže uvedený vzorec a stiskněte Ctrl + směna + vstoupit or vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste získali počty dalších buněk.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Poznámky:

---

2 Extrahujte číslo zprava od řetězce

Jak je znázorněno na obrázku níže, chcete-li extrahovat pouze čísla, která se objeví za textem v buňce, vyzkoušejte následující vzorec.

Obecný vzorec

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

Argumenty

Poznámka:

Vyberte prázdnou buňku, zadejte níže uvedený vzorec a stiskněte Ctrl + směna + vstoupit or vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste získali počty dalších buněk.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Poznámky:

---

3. Extrahujte všechna čísla z libovolné pozice v textovém řetězci

Výše uvedené metody pomáhají extrahovat číslo pouze zleva nebo zprava od textového řetězce. Pokud chcete extrahovat všechna čísla odkudkoli v textovém řetězci, nabízíme vám tři způsoby, jak to udělat.

3.1 Extrahujte všechna čísla odkudkoli v řetězci pomocí vzorce

Následující vzorec můžete použít k extrahování všech čísel z libovolného místa v textovém řetězci v aplikaci Excel.

1. Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte vstoupit klíč k získání všech čísel z buňky B5.

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

2. Vyberte výslednou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste získali všechna čísla ostatních buněk.

3.2 Extrahujte všechna čísla odkudkoli v řetězci pomocí VBA

Výše uvedený vzorec je pro mnoho uživatelů Excelu příliš dlouhý a komplikovaný. Ve skutečnosti můžete spustit skript VBA pro automatizaci úlohy v Excelu. Můžete postupovat následovně.

1. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2. V otvoru Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul. Poté zkopírujte VBA níže do okna Kód modulu.

Kód VBA: Extrahujte všechna čísla odkudkoli v textovém řetězci

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. zmáčkni F5 klíč ke spuštění kódu. V otvoru Kutools pro Excel V dialogovém okně vyberte oblast buněk, ze které chcete extrahovat všechna čísla z každé buňky, a potom klepněte na OK .

4. Pak další Kutools pro Excel objeví se dialogové okno. V tomto dialogovém okně vyberte cílovou buňku a klikněte OK.

Poté jsou hromadně extrahována všechna čísla z každé buňky ve zvoleném rozsahu.

---

4. Extrahujte čísla za určitým textem

Jak je znázorněno na snímku obrazovky níže, chcete-li extrahovat čísla za konkrétním textem „Ne.“, tato část poskytuje dva způsoby, jak to udělat.

4.1 Extrahujte čísla za určitým textem pomocí vzorce

Následující vzorec můžete použít k extrahování čísel za určitým textem v buňce v Excelu.

Obecný vzorec:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

Argumenty

Vyberte prázdnou buňku, zkopírujte nebo zadejte níže uvedený vzorec a stiskněte klávesu vstoupit klíč k získání výsledku. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Poznámky:

4.2 Extrahování čísel za určitým textem s uživatelsky definovanou funkcí

Následující uživatelsky definovaná funkce může také pomoci extrahovat čísla za určitým textem v buňce. Postupujte prosím následovně.

1. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul, a poté zkopírujte níže uvedený kód VBA do okna Kód modulu.

Kód VBA: extrahujte čísla za určitým textem v buňce

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. zmáčkni Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.

4. Vyberte buňku, zadejte níže uvedený vzorec a stiskněte vstoupit klíč. Vyberte tuto výsledkovou buňku a poté přetáhněte její úchyt automatického vyplňování dolů, abyste použili tento vzorec na další buňky.

=GetNumberAfterTheChar(B5,"No. ")

Poznámky:

---

Související články: