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 ]
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
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
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.
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
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
=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.
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
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
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
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
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.
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
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
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
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
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
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:
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
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:
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.
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:
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
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.
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.
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
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.
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:
- A szám a szöveg elején található;
- A szám a szöveg végén található;
- 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
Jegyzet:
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:
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
Jegyzet:
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:
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
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:
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:
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
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...
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!