Ugrás a tartalomra

Excel oktatóanyag – szöveg vagy szám kinyerése adott pozíció szerint

Sok esetben előfordulhat, hogy csak a hasznos tartalmat kell kinyernie egy mondatból vagy egy cellában lévő szöveges karakterláncból, például a címből a tartományt, az e-mail címet egy mondatból, a futárszámla számát egy beszélgetésből, stb. Ez az oktatóanyag leszűkíti a kibontást a cellában egy adott pozícióra, és különböző módszereket gyűjt össze, amelyek segítségével szöveget vagy számokat kinyerhet egy cellából az Excel adott pozíciója szerint.

Tartalomjegyzék: [ Elrejt ]

(Kattintson az alábbi tartalomjegyzék bármely címére, vagy a jobb oldalon a megfelelő fejezethez navigáljon.)

Szöveg kibontása pozíció szerint

Ez a rész azokat a gyakori pozíciókat gyűjti össze, ahol a szöveg kivonható egy cellából, és megfelelő módszereket kínál ezek lépésről lépésre történő kezelésére. További részletekért böngészhet.

1. Vegye ki a karakterek számát balról vagy jobbról

A karakterek számának kivonásához egy karakterlánc bal vagy jobb oldaláról próbálja ki az alábbi módszerek egyikét.

1.1 Az első vagy az utolsó N karakter kibontása képletekkel

Tegyük fel, hogy van egy szöveges karakterlánc-lista a B oszlopban, amint az az alábbi képernyőképen látható, az első 2 karakter és az utolsó 2 karakter kinyeréséhez az egyes karakterláncokból, akkor a következő képleteket alkalmazhatja.

Kivonja az első N karaktert egy szöveges karakterláncból

A LEFT funkció segíthet az első N karakter egyszerű kinyerésében egy szöveges sztringből az Excelben.

Általános képlet

=LEFT(text_string,[num_chars])

érvek

Szöveg_húr: A kivonatolni kívánt karaktereket tartalmazó szöveges karakterlánc. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
Karakterek száma: Meghatározza a kivonni kívánt karakterek számát.
A Num_chars értékének nullánál nagyobbnak vagy egyenlőnek kell lennie;
Ha a megadott Num_chars nagyobb, mint a szöveges karakterlánc hossza, akkor a teljes szöveget adja vissza;
Ha a karakterek száma ki van hagyva, akkor azt 1-nek kell tekinteni.

Most alkalmazhatja ezt a képletet az első 2 karakter kinyerésére a B oszlop celláiból.

1. Jelöljön ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az első eredmény eléréséhez. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

=LEFT(B5,2)

Most a B2:B5 tartomány celláinak első 10 karaktere ki lett bontva.

Kivonja az utolsó N karaktert egy szöveges karakterláncból

Itt a RIGHT függvényt alkalmazzuk az utolsó N karakter kinyerésére egy szöveges sztringből az Excelben.

Általános képlet

=RIGHT(text_string,[num_chars])

érvek

Szöveg_húr: A kivonatolni kívánt karaktereket tartalmazó szöveges karakterlánc. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
Karakterek száma: Meghatározza a kivonni kívánt karakterek számát.
Az Karakterek száma nullánál nagyobbnak vagy egyenlőnek kell lennie;
Ha a megadott Karakterek száma nagyobb, mint a szöveges karakterlánc hossza, akkor a teljes szöveget adja vissza;
Ha a Karakterek száma ki van hagyva, akkor azt 1-nek kell tekinteni.

Válasszon ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Ezután jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltés fogantyúját, hogy további eredményeket kapjon.

=RIGHT(B5,2)

1.2 Bontsa ki az első vagy az utolsó N karaktert egy csodálatos eszközzel

Bár a fenti képletek egyszerűek, az első vagy az utolsó n karaktert a szöveges karakterláncok hosszú listájából kinyerni, továbbra is húznia kell az Automatikus kitöltés fogantyúját felülről lefelé, ami talán egy kicsit időigényes. Itt ajánlja Kutools az Excel számára'S Kivonat szöveg segédprogram, amely segít tömegesen kivonni az első vagy az utolsó N karaktert a szöveges karakterláncok listájából.

1. Válassza ki előre azt a szöveges karakterlánc-listát, amelyből szöveget szeretne kivonni, és kattintson a gombra Kutools > szöveg > Kivonat szöveg.

2. A felbukkanóban Kivonat szöveg párbeszédpanelen az alábbiak szerint kell konfigurálnia.

2.1) Győződjön meg róla, hogy a Kivonat hely szerint fül;
2.2) A Választék mezőben megjelenik a kiválasztott tartomány, igény szerint válthat másik tartományra;
2.3) A Opciók szakasz:
Ha ki szeretné bontani az első N karaktert, válassza a Az első N karakter választógombot, majd a szövegmezőben adja meg a kivonni kívánt karakterek számát. Ebben az esetben a 2-es számot írom be;
Ha ki szeretné bontani az utolsó N karaktert, válassza ki a Az utolsó N karakter választógombot, majd a szövegmezőben adja meg a kivonni kívánt karakterek számát. Ebben az esetben az utolsó 2 karaktert szeretném kivonni a szöveges karakterláncból, ezért a 2-es számot írom be a szövegmezőbe.
2.4) Kattintson OK. Lásd a képernyőképet:

Megjegyzések: Ha a szöveges karakterlánc megváltozásakor az eredményt dinamikussá szeretné tenni, ellenőrizze a Beszúrás képletként doboz.

3. A következő felbukkanóban Kivonat szöveg párbeszédpanelen válasszon ki egy cellát a kivont karakterek kiadásához, majd kattintson a gombra OK.

Ezután a megadott első vagy utolsó N karakter tömegesen kivonásra kerül a kiválasztott cellákból.

Kattintson, ha többet szeretne megtudni erről a szolgáltatásról.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.


2. Szöveg kibontása egy bizonyos karakter/szó előtt vagy után

Ha egy bizonyos karakter vagy szó előtt vagy után szeretne szöveget kivonni, az ebben a szakaszban található különböző forgatókönyvek megfelelnek az Ön igényeinek.

2.1 Szöveg kibontása az első határoló (karakter) előtt vagy után

Amint az alábbi képernyőképen látható, a B4:B10 tartomány egyes celláiból az első határoló előtti vagy utáni szöveg kinyeréséhez az alábbi módszerek egyikét alkalmazhatja.

2.1.1 Szöveg kibontása az első határoló előtt képlettel

A BAL és a KERESÉS függvényeken alapuló képlet alkalmazása segíthet az első határoló előtti szöveg kivonatában egy cellából. Ennek végrehajtásához kövesse az alábbi lépéseket.

Általános képlet

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

érvek

Szöveg_húr: A szöveges karakterlánc, amelyből ki szeretné bontani az alkarakterláncot. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
delimiter: Az első határoló, amely meghatározza, hogy melyik szöveg kerül kinyerésre egy cellából.

Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs az első eredmény eléréséhez. Jelölje ki az első eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy a szövegek a többi cella első határolója elé kerüljenek.

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

2.1.2 Szöveg kibontása az első határoló után képlettel

Az alábbi képlet segít az első határoló utáni szöveg kinyerésében az Excel cellájából.

Általános képlet

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

érvek

Szöveg_húr: A szöveges karakterlánc, amelyből ki szeretné bontani a részkarakterláncot. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
Határoló: Az első határoló, amely meghatározza, hogy melyik szöveg kerül kinyerésre egy cellából.
Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs az első eredmény eléréséhez. Jelölje ki az első eredménycellát, és húzza le az Automatikus kitöltési fogantyút a többi eredmény megjelenítéséhez.

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

2.1.3 Szöveg kibontása az első határoló előtt vagy után egy csodálatos eszközzel

Itt nagyon ajánljuk a Kivonat szöveg hasznossága Kutools az Excelhez. Ezzel a funkcióval könnyedén, tömegesen kivonhatja az első határoló előtti vagy utáni szövegeket a cellákból.

1. Válassza ki a cellák tartományát, ahonnan ki szeretné bontani a szöveget, majd kattintson a gombra Kutools > szöveg > Kivonat szöveg.

2. Ban,-ben Kivonat szöveg párbeszédpanelen az alábbiak szerint kell konfigurálnia.

2.1) Maradjon a Kivonat hely szerint fül;
2.2) A Választék mezőben megjelenik a kiválasztott tartomány, szükség szerint módosíthatja;
2.3) A Opciók szakasz:
Az első határoló előtti szöveg kibontásához válassza a lehetőséget a szöveg előtt rádiógombot, majd írja be az első határolót a szövegmezőbe;
Az első határoló utáni szöveg kivonásához válassza a lehetőséget a szöveg után választógombot, majd írja be az első határolót a szövegmezőbe.
2.4) Kattintson OK.

Jegyzet: Ha az eredményt dinamikussá szeretné tenni a karakterlánc megváltozásakor, jelölje be a Beszúrás képletként négyzetet.

3. Aztán még egy Kivonat szöveg párbeszédpanel jelenik meg, válasszon ki egy cellát az eredmények megjelenítéséhez, és kattintson OK.

Ezután az első határoló jel előtti vagy utáni szövegek egyszerre kivonásra kerülnek a kijelölt cellákból.

Ha többet szeretne megtudni erről a funkcióról, látogasson el a következő oldalra: Gyorsan kivonat bizonyos szövegeket az Excel celláiból.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.

2.2 Szöveg kibontása az utolsó határoló (karakter) előtt vagy után

A fenti lépésekben megtanultuk a cellából az első határoló előtti vagy utáni szöveg kinyerésének módszereit. Amint az alábbi képernyőképen látható, ez a szakasz két képletet mutat be a cellák utolsó határolója előtti vagy utáni szöveg kinyeréséhez. Ennek elvégzéséhez kövesse az alábbi lépéseket.

2.2.1 Az utolsó határoló előtti szöveg kibontása képlettel

Ha az utolsó határoló előtti szöveget ki szeretné bontani egy cellából, használhatja a KERESÉS, HOSSZÚ és SUBSTITUTE függvényeket a LEFT függvényen belül.

Általános képlet

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

érvek

Szöveg_húr: A szöveges karakterlánc, amelyből ki szeretné bontani az alkarakterláncot. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
Határoló: Az utolsó határoló, amely meghatározza, hogy melyik szöveg kerül kinyerésre egy cellából.

Válasszon ki egy cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy kivonja a szövegeket az ugyanabban az oszlopban lévő többi szöveges karakterláncból.

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

2.2.2 Szöveg kibontása az utolsó határoló után képlettel

Miután kibontotta az utolsó határoló előtti szöveget egy cellából, az alábbi képlet segítségével igény szerint kivonhatja az utolsó határoló utáni szöveget.

Általános képlet

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

érvek

Szöveg_húr: A szöveges karakterlánc, amelyből ki szeretné bontani az alkarakterláncot. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
Határoló: Az utolsó határoló, amely meghatározza, hogy melyik szöveg kerül kinyerésre egy cellából.

Válasszon ki egy cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy kivonja a szövegeket az ugyanabban az oszlopban lévő többi szöveges karakterláncból.

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

2.3 Szöveg kibontása az n-edik karakter után

Nézze meg az alábbi példát, a B4:B10 tartományban található szöveges karakterláncok listája, a harmadik karakter utáni szöveg kivonásához minden cellából alkalmazhat egy képletet a MID függvényen és a LEN függvényen.

Általános képlet

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

érvek

Szöveg_húr: A szöveges karakterlánc, amelyből ki szeretné bontani az alkarakterláncot. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
nth_char: A szám az n-edik karaktert jelöli, és ki kell bontani az utána lévő szöveget.

Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltés fogantyúját, hogy további eredményeket kapjon.

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

2.4 Kivonja az n-edik szót egy szöveges karakterláncból

Feltételezve, hogy van egy szöveges karakterlánc listája, ahogy az alábbi képernyőképen látható, és csak az n-edik szót szeretné kivonni a szöveges karakterláncból, ez a rész három módszert kínál a megvalósításhoz.

2.4.1 Az n-edik szó kibontása képlettel

A TRIM, MID, SUBSTITUTE, REPT és LEN függvények kombinálásával kivonhatja az n-edik szót egy cellában lévő szöveges karakterláncból.

Általános képlet

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

érvek

Szöveg_húr: Az a szöveglánc, amelyből az n-edik szót ki szeretné bontani. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
N: A szám az n-edik szót jelöli, amelyet ki kell bontani a szövegből.

Ebben az esetben a B5:B10 tartomány tartalmazza a szöveges karakterláncokat, a D5:D10 pedig az n-edik szót reprezentáló számokat, alkalmazzuk ezt a képletet az n-edik szó kinyerésére a szöveges karakterláncból.

Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs az első eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a többi cella n-edik szavát.

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

Jegyzet: Az n-edik számot közvetlenül beírhatja a képletbe az alábbiak szerint.

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

2.4.2 Az n-edik szó kibontása a felhasználó által definiált függvénnyel

A fenti képlet mellett egy felhasználó által definiált függvényt is alkalmazhat az n-edik szó kinyerésére az Excel cellájából.

1. megnyomni a más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modul, majd másolja be az alábbi VBA-t a Code ablakba.

VBA-kód: Kivonja az n-edik szót egy cellában lévő szöveges karakterláncból

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. megnyomni a más + Q gombok a Microsoft Visual Basic for Applications ablak.

4. Menjen vissza arra a munkalapra, amely tartalmazza azokat a szöveges karakterláncokat, amelyekből ki szeretné bontani az n-edik szót. Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs az n-edik szó megszerzéséhez.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Jegyzet: a képletben D5 az a cella, amely az n-edik szót reprezentáló számot tartalmaz. Alternatív megoldásként közvetlenül lecserélheti a cellahivatkozást egy számra.

5. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút, hogy kivonja az n-edik szót a többi cella szöveges karakterláncaiból.

2.4.3 Kivonja az n-edik szót egy csodálatos eszközzel

Ha nem szeretne manuálisan alkalmazni egy képletet vagy egy fent megadott, felhasználó által definiált függvényt, itt ajánljuk a Kutools for Excel alkalmazást. Bontsa ki a cellában az n-edik szót hasznosság. Ezzel a funkcióval egyszerűen, néhány kattintással kinyerheti az n-edik szót egy cellában lévő szöveges karakterláncból.

1. Jelöljön ki egy cellát az eredmény elhelyezéséhez, és kattintson a gombra Kutools > Formula Segítő > szöveg > kivonat az n-edik szó a cellában. Lásd a képernyőképet:

2. Ban,-ben Képletek segítője párbeszédpanelen az alábbiak szerint kell konfigurálnia.

2.1) A Válasszon egy képletlistát doboz, a Bontsa ki a cellában az n-edik szót opció ki van jelölve;
2.2) A Sejt mezőben válasszon egy cellát, amely tartalmazza azt a szöveges karakterláncot, amelyből ki szeretné bontani az n-edik szót;
2.3) A Az N mezőben válassza ki az n-edik számot tartalmazó cellát, vagy írja be közvetlenül az Ön igényeinek megfelelő számot;
2.4) Kattintson OK.

3. Ezután az n-edik (második) szó kinyerésre kerül a B5 cellában lévő szöveges karakterláncból, és ezzel egyidejűleg egy képlet keletkezik. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút az n-edik szó lekéréséhez más szöveges karakterláncokból.

Kattintson, ha többet szeretne megtudni erről a szolgáltatásról.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.

2.5 Szöveg kibontása a határoló n-edik előfordulása előtt vagy után

Tegyük fel, hogy van egy listája a szöveges karakterláncokról, ahogy az az alábbi képernyőképen látható. A szóköz második előfordulása előtti vagy utáni szöveg kinyeréséhez ez a szakasz két képletet tartalmaz, amelyek segítenek ennek végrehajtásában.

2.5.1 Szöveg kibontása a határoló n-edik előfordulása előtt

Használhatja a LEFT függvényt, valamint a SUBSTITUTE és FIND függvényeket, hogy az n-edik elválasztójel előtti szöveget kivonja egy cellából az Excelben.

Általános képlet

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

érvek

Szöveg_húr: A szöveg, amelyből ki szeretné bontani a szöveget. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
N: A szám az elválasztójel n-edik előfordulását jelenti, amely előtt ki kell bontani a szöveget.

Válasszon ki egy cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltés fogantyúját, hogy további eredményeket kapjon a listában.

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

Jegyzet: A képletben B5 az a cella, amely azt a szöveges karakterláncot tartalmazza, amelyből a szöveget ki szeretné bontani; A „ ” itt egy szóközt jelöl, a 2 pedig a szóköz második előfordulását. Igényei szerint módosíthatja őket.

2.5.2 Szöveg kibontása a határoló n-edik előfordulása után

Ha a határoló n-edik előfordulása után szeretne szöveget kivonni, alkalmazhatja a JOBBRA függvényt a SUBSTITUTE, LEN és FIND függvényekkel.

Általános képlet

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

érvek

Szöveg_húr: A szöveg, amelyből ki szeretné bontani a szöveget. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;
N: A szám egy határoló n-edik előfordulását jelenti, amely után szöveget fog kivonni.

Most alkalmazhatja ezt a képletet a szóköz második előfordulása utáni szöveg kinyerésére a B5:B10 tartomány egyes celláiból az alábbiak szerint.

Válasszon ki egy cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltés fogantyúját, hogy további eredményeket kapjon.

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

2.6 Szöveg kibontása a sortörés előtt vagy után

Tegyük fel, hogy van egy rendelési listája a B oszlopban, és csak a dátumrészt és a termékszám részt szeretné kivonni minden cellából. Használhatja az alábbi Excel képleteket a végrehajtásához.

2.6.1 Szöveg kibontása az első sortörés előtt képlettel

Amint a fenti képernyőképen látható, a dátum rész a cellán belüli első sortörés előtt található. Ez a rész bemutatja a LEFT funkciót a KERESÉS funkcióval együtt, amely segít a szöveg kibontásában a cellán belüli első sortörés előtt.

Általános képlet

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

érvek

Mobil: Az a cella, amelyből az első sortörés előtt ki szeretné bontani a szöveget.

Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

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

Ezután láthatja a szöveget az első sortörés előtt a B5:B8 tartomány minden cellájában, amint az alábbi képernyőképen látható.

Jegyzet: A képletben a CHAR(10) sortörést jelöl a Windows rendszeren.

2.6.2 Az utolsó sortörés utáni szöveg kibontása képlettel

Az előző lépésben arról beszéltünk, hogyan lehet szöveget kivonni egy cellán belül az első sortörés előtt. Ez a rész pedig végigvezeti Önt, hogyan bontsa ki az utolsó sortörés utáni szöveget egy cellán belül, eltérő képlettel.

Általános képlet

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

érvek

Mobil: Az a cella, amelyből az első sortörés előtt ki szeretné bontani a szöveget.

Válasszon ki egy üres cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

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

Ezután a termék sz. a lista egyes celláinak egy része kibontásra kerül a fenti képernyőképen látható módon.

Jegyzet: A képletben a CHAR(10) sortörést jelöl a Windows rendszeren.

2.7 Szöveg kibontása egy szó előtt vagy után

Az előző részekben megtanultuk, hogyan lehet egy karakter vagy határoló előtt vagy után szöveget kivonni. Mit kell tennie a szöveg kibontásához egy teljes szó előtt vagy után? Ez a rész három módszert mutat be, amelyek segítenek ennek a feladatnak a végrehajtásában.

2.7.1 Szöveg kibontása egy adott szó előtt képlettel

A következő képlet segít egy bizonyos szó előtti szöveg kibontásában az Excelben.

Általános képlet

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

érvek

Mobil: Az a cella, amelyből ki szeretné bontani a szöveget egy bizonyos szó előtt.
szó: A szó, amely előtt az összes szöveget ki szeretné bontani. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;

Válasszon ki egy üres cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

Ebben a példában az „Excel” szó előtti összes szöveget ki fogjuk bontani, így közvetlenül beírjuk a szót a képletbe, és idézőjelek közé tesszük. Vagy hivatkozhat egy cellára, amely az „Excel” szót tartalmazza.

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

Megjegyzések:

1) Ez a képlet megkülönbözteti a kis- és nagybetűket.
2) Ha a beírt szó ismétlődéseket tartalmaz ugyanabban a cellában, a képlet csak az első előfordulás után vonja ki a szöveget.

2.7.2 Szöveg kibontása egy adott szó után képlet segítségével

Ha egy bizonyos szó után szeretne szöveget kivonni, akkor a következő képlet segítségével végezheti el.

Általános képlet

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

érvek

Mobil: A cella, amelyből egy bizonyos szó után ki szeretné bontani a szöveget.
szó: A szó, amely után az összes szöveget ki szeretné bontani. Lehet cellahivatkozás vagy tényleges szöveges karakterlánc dupla idézőjelbe zárva;

Válasszon ki egy cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút, hogy ezt a képletet más cellákra is alkalmazza.

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

Ezután láthatja az összes szöveget, miután az egyes cellákban található „Excel” szó ki lett bontva az alábbi képernyőképen látható módon.

Megjegyzések:

1) Ez a képlet nem tesz különbséget a kis- és nagybetűk között.
2) Ha a beírt szó ismétlődéseket tartalmaz ugyanabban a cellában, a képlet csak az első előfordulás után vonja ki a szöveget.

2.7.3 Szöveg kibontása egy bizonyos szó előtt vagy után egy csodálatos eszközzel

Ha úgy érzi, hogy a képlet használata sok kellemetlenséget okozhat, itt erősen ajánljuk a Kivonat szöveg hasznossága Kutools az Excel számára. Ez a funkció néhány kattintással automatizálja az Extraction feladatot az Excelben.

1. kettyenés Kutools > szöveg > Kivonat szöveg a funkció engedélyezéséhez.

2. Ban,-ben Kivonat szöveg párbeszédpanelen hajtsa végre a következő beállításokat.

2.1) Győződjön meg arról, hogy a Kivonat hely szerint fül;
2.2) A Választék jelölje be a ikont gombbal válassza ki a cellák tartományát, ahonnan a szövegeket ki szeretné bontani;
2.3) A Opciók szakasz:
A szó előtti összes szöveg kibontásához válassza a lehetőséget a szöveg előtt rádiógombot, majd írja be a szót a szövegmezőbe;
A szó utáni összes szöveg kibontásához válassza a lehetőséget a szöveg után rádiógombot, majd írja be a szót a szövegmezőbe.
2.4) Kattintson a gombra OK gomb. Lásd a képernyőképet:

Megjegyzések: Ha dinamikus eredményeket szeretne létrehozni, ellenőrizze a Beszúrás képletként doboz. Ezután az eredmények automatikusan frissülnek, amikor a tartományban lévő adatok megváltoznak.

3. Ezután egy Kivonat szöveg párbeszédpanel jelenik meg, ki kell választania egy cellát az eredmény kimenetéhez, majd kattintson a gombra OK gombot.

Ezután a kiválasztott tartomány egyes celláiban egy bizonyos szó előtt vagy után lévő szövegek azonnal kibontásra kerülnek.

Jegyzet: Ez a funkció megkülönbözteti a kis- és nagybetűket.

Kattintson, ha többet szeretne megtudni erről a szolgáltatásról.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.


3. Karakterek/szavak közötti kivonat

Ha bizonyos karakterek vagy szavak között szeretne szöveget kivonni, próbálkozzon a következő módszerekkel.

3.1 Szöveg kibontása két karakter között

Szöveg kinyerése két karakter között, amelyek lehetnek azonosak vagy különböző karakterek. Ez a rész több módszert kínál, amelyek közül Ön igényei szerint választhat egyet.

3.1.1 Szöveg kibontása két azonos karakter között képlettel

Amint az alábbi képernyőképen látható, a B oszlopban található a szöveges karakterláncok listája, és a „/” karakterek közötti számrészt a tartomány minden cellájából szeretné kivonni, a következő képlet jót tehet.

Válasszon ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki az eredménycellát, majd húzza le az Automatikus kitöltés fogantyúját a lista többi cellájának eredményének megjelenítéséhez.

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

Ezután a két azonos „/” karakter közötti szöveget a rendszer kivonja a tartomány minden cellájából. Lásd a képernyőképet:

Jegyzet:

1) B5 az a cella, amelyből két azonos karakter közötti szöveget szeretne kivonni;
2) "/” két azonos karakter, amelyek között szöveget szeretne kivonni.
Ezeket a változókat saját adatai alapján kell módosítania.

3.1.2 Szöveg kibontása két különböző karakter között képlet segítségével

Miután megtanulta, hogyan lehet szöveget kivonni két azonos karakter között egy cellán belül, itt bemutatunk egy képletet a két különböző karakter közötti szöveg kinyerésére. Az alábbi képernyőképen látható módon, ha csak a „<” és „>” jelek közötti e-mail-címet szeretné kivonni a B oszlop egyes celláiból, akkor tegye a következőket.

Általános képlet

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

érvek

Mobil: A cella, amelyből ki szeretné bontani a szöveget két különböző karakter között;
End_char: A két különböző karakter végkaraktere;
Start_char: A két különböző karakter kezdő karaktere.

Válasszon ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

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

Láthatja, hogy csak a megadott karakterek közötti szöveg kerül kibontásra a fenti képernyőképen látható módon.

3.1.3 Szöveg kibontása két karakter között egy csodálatos eszközzel

Itt nagyon ajánljuk a Húzza ki a karakterláncokat a megadott szöveg között jellemzője Kutools az Excel számára segítségével egyszerűen kivonhat szöveget két azonos vagy különböző karakter között egy cellán belül az Excelben.

1. Válasszon egy üres cellát az eredmény megjelenítéséhez, majd kattintson a gombra Kutools > Formula Segítő > Formula Segítő.

2. Ban,-ben Képletek segítője párbeszédpanelen hajtsa végre a következő beállításokat.

2.1) Ellenőrizze a Szűrő mezőbe, majd írja be a szót "Kivonat" a szövegmezőbe;
2.2) A Válasszon egy képletlistát jelölje be a ikont Húzza ki a karakterláncokat a megadott szöveg között választási lehetőség;
2.3) A Érvek bevitele szakasz:
Ban,-ben Sejt mezőbe válasszunk ki egy cellát, ahonnan ki szeretnénk bontani a szöveget (itt a B5 cellát választom);
Ban,-ben Kezdő karakter (ek) mezőbe írja be a két különböző karakter kezdő karakterét;
Ban,-ben vég char(s) mezőbe írja be a két különböző karakter záró karakterét.
2.4) Kattintson Ok. Lásd screenshot:

3. Ezután csak a „<” és „>” közötti szöveg kerül kibontásra a B5 cellán belül. Időközben létrejött egy képlet, kiválaszthatja ezt az eredménycellát, majd lefelé húzva az Automatikus kitöltési fogantyút, hogy szövegeket kinyerhessen a lista többi cellájából.

Kattintson, ha többet szeretne megtudni erről a szolgáltatásról.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.

3.1.4 Szöveg kibontása két karakter között (beleértve a karaktereket is) szabály segítségével

Ha meg szeretné tartani a két karaktert a kibontás után, próbáljon meg egy szabályt alkalmazni a Kutools for Excel program Szöveg kivonat funkciójában.

1. kettyenés Kutools > szöveg > Kivonat szöveg.

2. Ban,-ben Kivonat szöveg párbeszédpanelen hajtsa végre a következő beállításokat.

2.1) Kattintson a gombra Kivonás szabály szerint fül;
2.2) A Választék szakaszban kattintson a gombbal válassza ki a cellák tartományát, ahonnan ki szeretné bontani a szöveget a karakterek között;
2.3) A szöveg mezőbe írja be <*>;
Tipp: "<"És">” azok a karakterek, amelyek között szöveget szeretne kivonni, a * egy helyettesítő karakter, amely tetszőleges számú karaktert jelöl. A feltételt igény szerint adhatja meg.
2.4) Kattintson a gombra hozzáad gombot a feltétel hozzáadásához a A szabály leírása lista mező;
2.5) Kattintson a gombra Ok gomb. Lásd a képernyőképet:

3. Egy másik Kivonat szöveg párbeszédpanel jelenik meg, válasszon ki egy cellát az eredmény kimenetéhez, majd kattintson a gombra OK gombot.

Ezután a megadott karakterek közötti szöveget (beleértve a karaktereket is) a rendszer tömegesen kivonja a kiválasztott tartomány minden cellájából.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.

3.2 Szöveg kibontása két szó között

A két karakter közötti szöveg kinyerése mellett előfordulhat, hogy két szó közötti szöveget is ki kell bontani. Például vegye ki a „KTE” és a „feature” szó közötti összes szöveges karakterláncot a B oszlop egyes celláiból, az alábbi képernyőképen látható módon. Megpróbálhatja az alábbi módszerek egyikét, hogy elvégezze.

3.2.1 Szöveg kibontása két szó között képlettel

A MID függvényen és a SEARCH függvényen alapuló képlet segítségével kivonhatja az összes szöveges karakterláncot egy cellán belül két szó között.

Általános képlet

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

érvek

Mobil: a cella, amelyből ki szeretné bontani az összes szöveges karakterláncot két szó között;
Kezdő_szó: annak a két szónak a kezdőszava, amely után az összes karakterláncot ki szeretné bontani;
Hossz1: a kezdőszó karakterhossza.
End_word: annak a két szónak a végszava, amely előtt ki szeretné bontani az összes szöveges karakterláncot.
Hossz2: a kezdőszó karakterhossza plusz 1.

Válasszon ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

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

Megjegyzések: A képletben a 3-as szám a "KTE" szó karakterhosszát jelenti; a 4-es szám a "KTE" szó karakterhosszát jelöli, plusz 1.

Láthatja, hogy a megadott két szó közötti összes szöveges karakterlánc ki lett bontva a B oszlop egyes celláiból.

3.2.2 Szöveg kibontása két szó között egy csodálatos eszközzel

Sok Excel-felhasználó számára nehéz lehet a képleteket megjegyezni és kezelni. Itt, a Húzza ki a karakterláncokat a megadott szöveg között jellemzője Kutools az Excel számára, egyszerűen, néhány kattintással kivonhatja a szöveget két szó között.

1. Jelöljön ki egy cellát az eredmény kiadásához, majd kattintson a gombra Kutools > Formula Segítő > Formula Segítő.

2. Ban,-ben Formula Segítő párbeszédpanelen az alábbiak szerint kell konfigurálnia.

2.1) Ellenőrizze a Szűrő mezőbe, majd írja be a szót "Kivonat" a szövegmezőbe;
2.2) A Válasszon egy képletlistát jelölje be a ikont Húzza ki a karakterláncokat a megadott szöveg között választási lehetőség;
2.3) A Érvek bevitele szakasz:
Ban,-ben Sejt mezőbe válasszunk ki egy cellát, ahonnan ki szeretnénk bontani a szöveget (itt a B5 cellát választom);
Ban,-ben Kezdő karakter (ek) mezőbe írja be annak a két szónak a kezdő szavát, amely után az összes szöveges karakterláncot ki szeretné bontani;
Ban,-ben záró karakter(ek) mezőbe írja be annak a két szónak a végét, amely előtt az összes karakterláncot ki szeretné bontani.
2.4) Kattintson Ok. Lásd screenshot:

3. Ezután a B5 cellában lévő két „KTE” és „feature” szó közötti összes szöveges karakterlánc kibontásra kerül. Időközben létrejött egy képlet, kiválaszthatja ezt az eredménycellát, majd lefelé húzva az Automatikus kitöltési fogantyút, hogy szövegeket kinyerhessen a lista többi cellájából.

  Ha ingyenes (30 napos) próbaverziót szeretne kapni a segédprogramról, kattintson a letöltéshez, majd lépjen a művelet végrehajtására a fenti lépések szerint.


Vonja ki a számokat pozíció szerint

Az alfanumerikus karakterláncok listájához három eset lehet:

  1. A szám a szöveg elején található;
  2. A szám a szöveg végén található;
  3. A szám bárhol szerepelhet a szövegben.

Ebben a részben különböző módszereket mutatunk be, amelyek segítségével a számok kinyerhetők minden fent említett esetben.

1 Vágjon ki számot egy karakterlánc bal oldaláról

Ez a rész egy képletet mutat be, amely segít a cellában csak a szöveg előtt megjelenő számok kinyerésében.

Általános képlet

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

érvek

Mobil: A cella, amelyből ki szeretné bontani a számot a szöveges karakterlánc bal oldalán.

Jegyzet:

1) Ha Excel 2019-et vagy korábbi verziót használ, meg kell nyomnia a gombot Ctrl + műszak + belép gombokat a tömbképlet megerősítéséhez.
2) Ha Excel 365-öt vagy Excel 2021-et használ, csak erősítse meg ezt a képletet a belép kulcs.

Válasszon ki egy üres cellát, írja be az alábbi képletet, és nyomja meg a gombot Ctrl + műszak + belép or belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a többi cella számának megtekintéséhez.

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

Megjegyzések:

1) Ha egy cella csak számokat tartalmaz, a rendszer az egész számot kivonja.
2) Ez a képlet csak a szöveges karakterlánc bal oldalán található számokat vonja ki. Ha a szöveg közepén vagy végén vannak számok, a rendszer figyelmen kívül hagyja azokat.

2 Vágja ki a számot a karakterlánc jobb oldaláról

Az alábbi képernyőképen látható módon, ha csak a cellában lévő szöveg után megjelenő számokat szeretné kivonni, próbálja ki a következő képletet.

Általános képlet

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

érvek

Mobil: Az a cella, amelyből számot szeretne kinyerni a szöveges karakterlánc elejéről.

Jegyzet:

1) Ha Excel 2019-et vagy korábbi verziót használ, meg kell nyomnia a gombot Ctrl + műszak + belép gombokat a tömbképlet megerősítéséhez.
2) Ha Excel 365-öt vagy Excel 2021-et használ, csak erősítse meg ezt a képletet az Enter billentyűvel.

Válasszon ki egy üres cellát, írja be az alábbi képletet, és nyomja meg a gombot Ctrl + műszak + belép or belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a többi cella számának megtekintéséhez.

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

Megjegyzések:

1) Ha egy cella csak számokat tartalmaz, a rendszer az egész számot kivonja.
2) Ez a képlet csak a szöveges karakterlánc jobb oldalán lévő számokat vonja ki. Ha a szöveg közepén vagy elején vannak számok, a rendszer figyelmen kívül hagyja azokat.

3. Vonja ki az összes számot a szöveg bármely helyéről

A fenti módszerek segítségével csak a szöveges karakterlánc bal vagy jobb oldaláról lehet számokat kivonni. Ha az összes számot bárhonnan ki szeretné gyűjteni egy szöveges karakterláncból, három módszert kínálunk ennek végrehajtására.

3.1 Az összes szám kinyerése egy karakterláncból képlet segítségével

A következő képlet segítségével kivonhatja az összes számot egy szöveges karakterláncból az Excelben.

1. Jelöljön ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép gombot, hogy az összes számot megkapja a B5 cellából.

=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. Jelölje ki az eredmény cellát, majd húzza le az Automatikus kitöltési fogantyút a többi cella összes számának megjelenítéséhez.

3.2 VBA segítségével kivonhatja az összes számot egy karakterláncból bárhonnan

A fenti képlet túl hosszú és bonyolult sok Excel-felhasználó számára. Valójában VBA-szkriptet futtathat a feladat automatizálásához az Excelben. A következőképpen teheti meg.

1. megnyomni a más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. A nyílásban Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modult. Ezután másolja be az alábbi VBA-t a Modulkód ablakba.

VBA-kód: Kivonja az összes számot bárhonnan egy szöveges karakterláncból

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. megnyomni a F5 gombot a kód futtatásához. A megnyitón KutoolsforExcel párbeszédpanelen válassza ki azt a cellatartományt, ahonnan az összes számot ki szeretné bontani az egyes cellákból, majd kattintson a gombra OK gombot.

4. Aztán még egy KutoolsforExcel párbeszédpanel jelenik meg. Ebben a párbeszédpanelen válasszon ki egy célcellát, és kattintson a gombra OK.

Ezután a rendszer tömegesen kivonja az összes számot a kiválasztott tartomány minden cellájából.


4. Vonja ki a számokat az adott szöveg után

Amint az alábbi képernyőképen látható, a „Szám” szöveg utáni számok kinyeréséhez ez a rész két módszert kínál a megvalósításhoz.

4.1 Számok kinyerése egy adott szöveg után képlettel

A következő képlet segítségével számokat vonhat ki egy adott szöveg után az Excelben.

Általános képlet:

=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}))

érvek

Mobil: A cella, amelyből számokat szeretne kinyerni egy adott szöveg után;
Szöveg: A szöveg, amely után számokat szeretne kivonni.

Válasszon ki egy üres cellát, másolja ki vagy írja be az alábbi képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

=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}))

Megjegyzések:

1) Ha egy cella nem tartalmazza az adott szöveget, a képlet #N/A értéket ad vissza.
2) A képlet megkülönbözteti a kis- és nagybetűket.

4.2 Számok kinyerése egy adott szöveg után a felhasználó által definiált funkcióval

A következő, felhasználó által definiált függvény szintén segíthet számok kinyerésében egy adott cella szövege után. Kérjük, tegye a következőket.

1. megnyomni a más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modul, majd másolja be az alábbi VBA-kódot a Modulkód ablakba.

VBA-kód: számok kinyerése egy adott szöveg után egy cellában

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. megnyomni a más + Q gombok a Microsoft Visual Basic for Applications ablak.

4. Válasszon ki egy cellát, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, majd húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

=GetNumberAfterTheChar(B5,"No. ")

Megjegyzések:

1) Ebben a képletben a B5 az a cella, ahonnan ki szeretné bontani a számot, és a „No. ” az a konkrét szöveg, amely után ki szeretné bontani a számot. Szükség szerint módosíthatja őket.
2) Ha az adott szöveg és a szám között határolójel van, kérjük, adja hozzá a határolót a szöveg végéhez. Ebben az esetben szóközt tettem a „No.” szöveg után, amely végül „Nem. ”.
3) Ez a módszer nem tesz különbséget a kis- és nagybetűk között;
4) Ha egy cella nem tartalmazza az adott szöveget, a képlet üres eredményt ad vissza.

Kapcsolódó cikkek:

Excel oktatóanyag: Szöveg-, szám- és dátumcellák felosztása (több oszlopra szétválasztás)
Ez az oktatóanyag három részre oszlik: felosztott szövegcellák, osztott számcellák és osztott dátumcellák. Mindegyik rész különböző példákat kínál, amelyek segítenek megtudni, hogyan kell kezelni a felosztási munkát, ha ugyanazzal a problémával találkozik.
Kattintson, ha többet szeretne megtudni ...

Excel Szöveg és szám hozzáadása a cella meghatározott pozíciójához
Az Excelben nagyon gyakori feladat a szövegek vagy számok hozzáadása a cellákhoz. Például szóköz hozzáadása a nevek között, előtag vagy utótag hozzáadása a cellákhoz, kötőjelek hozzáadása a közösségi számokhoz. Ebben az oktatóanyagban szinte az összes forgatókönyvet felsorolja az Excelben való hozzáadáshoz, és megadja a megfelelő módszereket.
Kattintson, ha többet szeretne megtudni ...

Excel Távolítsa el a karaktereket, szavakat, számokat a szöveges karakterláncokból
Tételezzük fel, hogy van egy hosszú listája a szöveges karakterláncokról, amelyek karaktereket, számokat vagy más speciális szimbólumokat tartalmaznak. Bizonyos esetekben előfordulhat, hogy el kell távolítania néhány karaktert a pozíció alapján, például jobbról, balról vagy középről a szöveges karakterláncokból, vagy törölnie kell néhány nem kívánt karaktert, számot a karakterláncok listájáról. A megoldások egyenkénti keresése megfájdítja a fejét, ez az oktatóanyag mindenféle módszert összegyűjt a karakterek, szavak vagy számok Excelben való eltávolítására.
Kattintson, ha többet szeretne megtudni ...

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations