Vadnica za Excel – izvlecite besedilo ali številko po določenem položaju

V mnogih primerih boste morda morali samo iz stavka ali besedilnega niza izvleči uporabno vsebino v celici, na primer iz naslova izluščiti pokrajino, iz stavka izvleči e-poštni naslov, iz pogovora izvleči številko kurirske računice, in tako naprej. Ta vadnica zoži ekstrakcijo na določen položaj v celici in zbere različne metode za pomoč pri ekstrakciji besedila ali številk iz celice glede na določen položaj v Excelu.

---

Ekstrahirajte besedilo po položaju

V tem razdelku so zbrani skupni položaji, kjer je besedilo mogoče izvleči iz celice, in nudi ustrezne metode za njihovo obravnavo korak za korakom. Za več podrobnosti lahko brskate.

1. Izvlecite število znakov z leve ali desne strani

Če želite izvleči število znakov z leve ali desne strani niza, lahko poskusite z eno od spodnjih metod.

1.1 Izvlecite prvih ali zadnjih N znakov s formulami

Predpostavimo, da imate seznam besedilnih nizov v stolpcu B, kot je prikazano na spodnjem posnetku zaslona, ​​da iz vsakega niza izvlečete prva 2 znaka in zadnja 2 znaka, lahko uporabite naslednje formule.

Izvlecite prvih N znakov iz besedilnega niza

Funkcija LEFT lahko pomaga enostavno izluščiti prvih N znakov iz besedilnega niza v Excelu.

Splošna formula

=LEFT(text_string,[num_chars])

Argumenti

Zdaj lahko uporabite to formulo za ekstrakcijo prvih dveh znakov iz celic v stolpcu B.

1. Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do prvega rezultata. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite formulo za druge celice.

=LEFT(B5,2)

Zdaj sta bila izvlečena prva 2 znaka v vsaki celici obsega B5:B10.

Izvlecite zadnjih N znakov iz besedilnega niza

Tukaj uporabimo funkcijo DESNO za ekstrakcijo zadnjih N znakov iz besedilnega niza v Excelu.

Splošna formula

=RIGHT(text_string,[num_chars])

Argumenti

Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Nato izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite druge rezultate.

=RIGHT(B5,2)

1.2 Izvlecite prvih ali zadnjih N znakov z neverjetnim orodjem

Čeprav so zgornje formule preproste, morate za ekstrakcijo prvih ali zadnjih n znakov iz dolgega seznama besedilnega niza še vedno povleči ročico samodejnega izpolnjevanja od zgoraj navzdol, kar je morda malo zamudno. Tukaj priporoča Kutools za ExcelJe Izvleček besedila pripomoček za pomoč pri ekstrakciji prvih ali zadnjih N znakov iz seznama besedilnih nizov v velikem obsegu.

1. Vnaprej izberite seznam besedilnih nizov, iz katerega želite izvleči besedilo, in kliknite Kutools > Besedilo > Izvleček besedila.

2. V pojavnem oknu Izvleček besedila pogovorno okno, morate konfigurirati na naslednji način.

Opombe: Če želite, da bo rezultat dinamičen, ko se besedilni niz spremeni, lahko preverite Vstavi kot formulo škatla.

3. V naslednjem pojavnem oknu Izvleček besedila pogovornem oknu izberite celico za izpis izvlečenih znakov in nato kliknite OK.

Nato se določenih prvih ali zadnjih N znakov ekstrahira iz izbranih celic v velikem obsegu.

Kliknite, če želite izvedeti več o tej funkciji.

---

2. Izvlecite besedilo pred ali za določenim znakom/besedo

Če želite izluščiti besedilo pred ali za določenim znakom ali besedo, bodo različni scenariji v tem razdelku ustrezali vašim potrebam.

2.1 Izvlecite besedilo pred ali za prvim ločilom (znakom)

Kot je prikazano na spodnjem posnetku zaslona, ​​lahko uporabite eno od spodnjih metod, da izvlečete besedilo pred ali za prvim ločilom iz vsake celice v obsegu B4:B10.

2.1.1 Izvlecite besedilo pred prvim ločilom s formulo

Uporaba formule, ki temelji na funkcijah LEFT in FIND, lahko pomaga ekstrahirati besedilo pred prvim ločilom iz celice. Če želite to narediti, lahko sledite spodnjim korakom.

Splošna formula

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

Argumenti

Izberite prazno celico, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ do prvega rezultata. Izberite prvo celico rezultata in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite besedila pred prvim ločilom drugih celic.

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

2.1.2 Izvlecite besedilo za prvim ločilom s formulo

Spodnja formula pomaga izvleči besedilo za prvim ločilom iz celice v Excelu.

Splošna formula

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

Argumenti

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

2.1.3 Izvlecite besedilo pred ali za prvim ločilom z neverjetnim orodjem

Tukaj zelo priporočamo Izvleček besedila uporabnost Kutools za Excel. S to funkcijo lahko preprosto ekstrahirate besedila pred ali za prvim ločilom iz vrste celic v velikem obsegu.

1. Izberite obseg celic, kamor želite izvleči besedilo, in kliknite Kutools > Besedilo > Izvleček besedila.

2. V Ljubljani Izvleček besedila pogovorno okno, morate konfigurirati na naslednji način.

Opomba: Če želite, da bo rezultat dinamičen, ko se besedilni niz spremeni, lahko potrdite polje Vstavi kot formulo.

3. Potem še eno Izvleček besedila Pojavi se pogovorno okno, izberite celico za izpis rezultatov in kliknite OK.

Nato se besedila pred ali za prvim ločilom izvlečejo iz izbranih celic naenkrat.

Če želite izvedeti več o tej funkciji, obiščite: Hitro ekstrahirajte določeno besedilo iz celic v Excelu.

2.2 Izvleči besedilo pred ali za zadnjim ločilom (znakom)

V zgornjih korakih smo se naučili metod za ekstrakcijo besedila pred ali za prvim ločilnikom iz celice. Kot je prikazano na spodnjem posnetku zaslona, ​​vam bo v tem razdelku prikazani dve formuli za ekstrakcijo besedila pred ali za zadnjim ločilom iz celice. Če želite to narediti, lahko sledite spodnjim korakom.

2.2.1 Izvlecite besedilo pred zadnjim ločilom s formulo

Če želite iz celice izvleči besedilo pred zadnjim ločilom, lahko uporabite funkcije ISKANJE, LEN in NADOMESTI znotraj funkcije LEVO.

Splošna formula

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

Argumenti

Izberite celico, vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da izvlečete besedila iz drugih besedilnih nizov v istem stolpcu.

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

2.2.2 Izvlecite besedilo za zadnjim ločilom s formulo

Ko iz celice izvlečete besedilo pred zadnjim ločilom, lahko uporabite spodnjo formulo, da po potrebi izvlečete besedilo za zadnjim ločilom.

Splošna formula

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

Argumenti

Izberite celico, vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da izvlečete besedila iz drugih besedilnih nizov v istem stolpcu.

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

2.3 Izvlecite besedilo za n-tim znakom

Poglejte spodnji primer, obstaja seznam besedilnih nizov v obsegu B4:B10, da iz vsake celice izvlečete besedilo po tretjem znaku, lahko uporabite formulo, ki temelji na funkciji MID in funkciji LEN.

Splošna formula

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

Argumenti

Izberite prazno celico, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite druge rezultate.

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

2.4 Izvlecite n-to besedo iz besedilnega niza

Predpostavimo, da imate seznam besedilnih nizov, kot je prikazano na spodnjem posnetku zaslona, ​​in želite iz besedilnega niza izvleči samo n-to besedo, ta razdelek ponuja tri načine, kako to narediti.

2.4.1 Izvlecite n-to besedo s formulo

Funkcije TRIM, MID, SUBSTITUTE, REPT in LEN lahko kombinirate, da izvlečete n-to besedo iz besedilnega niza v celici.

Splošna formula

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

Argumenti

V tem primeru obseg B5:B10 vsebuje besedilne nize, D5:D10 vsebuje številke, ki predstavljajo n-to besedo, uporabimo to formulo, da izvlečemo n-to besedo iz besedilnega niza.

Izberite prazno celico, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ do prvega rezultata. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite n-to besedo drugih celic.

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

Opomba: V formulo lahko neposredno vnesete n-to številko, kot sledi.

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

2.4.2 Izvlecite n-to besedo z uporabniško definirano funkcijo

Poleg zgornje formule lahko uporabite tudi uporabniško definirano funkcijo za ekstrakcijo n-te besede iz celice v Excelu.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul, in nato kopirajte spodnji VBA v okno kode.

Koda VBA: izvlecite n-to besedo iz besedilnega niza v celici

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. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Vrnite se na delovni list, ki vsebuje besedilne nize, iz katerih želite izvleči n-to besedo. Izberite prazno celico, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ, da dobite n-to besedo.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Opomba: v formuli je D5 celica, ki vsebuje številko, ki predstavlja n-to besedo. Lahko pa tudi neposredno zamenjate sklic na celico s številko.

5. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da izvlečete n-to besedo iz besedilnih nizov drugih celic.

2.4.3 Izvlecite n-to besedo z neverjetnim orodjem

Če ne želite ročno uporabiti formule ali uporabniško definirane funkcije, navedene zgoraj, tukaj priporoča Kutools za Excel Izvlecite n-to besedo v celici uporabnost. S to funkcijo lahko preprosto izvlečete n-to besedo iz besedilnega niza v celici samo z nekaj kliki.

1. Izberite celico, v katero želite postaviti rezultat, in kliknite Kutools > Pomočnik za formulo > Besedilo > Izvleček n-to besedo v celici. Oglejte si posnetek zaslona:

2. V Ljubljani Pomočnik za formule pogovorno okno, morate konfigurirati na naslednji način.

3. Nato se n-ta (druga) beseda izvleče iz besedilnega niza v celici B5 in lahko vidite, da je bila hkrati ustvarjena formula. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite n-to besedo iz drugih besedilnih nizov.

Kliknite, če želite izvedeti več o tej funkciji.

2.5 Izvlecite besedilo pred ali po n-ti pojavitvi ločila

Predpostavimo, da imate seznam besedilnih nizov, kot je prikazano na spodnjem posnetku zaslona. Če želite izvleči besedilo pred ali po drugi pojavitvi presledka, ta razdelek ponuja dve formuli, ki vam pomagata pri tem.

2.5.1 Izvlecite besedilo pred n-to pojavljanjem ločila

Funkcijo LEVO lahko uporabite skupaj s funkcijama SUBSTITUTE in FIND, da izvlečete besedilo pred n-tim pojavom ločila iz celice v Excelu.

Splošna formula

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

Argumenti

Izberite celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in jo povlecite Ročaj samodejnega izpolnjevanja navzdol, da dobite druge rezultate na seznamu.

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

Opomba: V formuli je B5 celica, ki vsebuje besedilni niz, iz katerega želite izvleči besedilo; “ ” tukaj predstavlja presledek, številka 2 pa predstavlja drugo pojavljanje presledka. Lahko jih spremenite glede na vaše potrebe.

2.5.2 Izvleči besedilo po n-ti pojavitvi ločila

Če želite izvleči besedilo po n-ti pojavitvi ločila, lahko uporabite funkcijo DESNO s funkcijami SUBSTITUTE, LEN in FIND.

Splošna formula

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

Argumenti

Zdaj lahko uporabite to formulo za ekstrakcijo besedila po drugem pojavu presledka iz vsake celice v obsegu B5:B10, kot sledi.

Izberite celico, vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in jo povlecite Ročaj samodejnega izpolnjevanja navzdol, da dobite druge rezultate.

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

2.6 Izvlecite besedilo pred ali po prelomu vrstice

Recimo, da imate seznam naročil v stolpcu B in želite iz vsake celice izvleči samo del datuma in del št. izdelka. Za to lahko uporabite spodnje formule Excela.

2.6.1 Izvlecite besedilo pred prelomom prve vrstice s formulo

Kot lahko vidite na zgornjem posnetku zaslona, ​​se datumski del nahaja pred prvim prelomom vrstice znotraj celice. V tem razdelku bo prikazana funkcija LEVO skupaj s funkcijo SEARCH, ki vam pomaga izvleči besedilo pred prelomom prve vrstice znotraj celice.

Splošna formula

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

Argumenti

Izberite prazno celico, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

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

Nato lahko vidite besedilo, preden se izvleče prvi prelom vrstice v vsaki celici v obsegu B5:B8, kot je prikazano na spodnjem posnetku zaslona.

Opomba: V formuli CHAR(10) predstavlja prelom vrstice v sistemu Windows.

2.6.2 Izvlecite besedilo po zadnjem prelomu vrstice s formulo

V prejšnjem koraku smo govorili o tem, kako izvleči besedilo pred prelomom prve vrstice znotraj celice. Ta del vas bo vodil, kako izvleči besedilo po zadnjem prelomu vrstice v celici z drugačno formulo.

Splošna formula

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

Argumenti

Izberite prazno celico, vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite formulo za druge celice.

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

Nato izdelek št. del vsake celice na seznamu se ekstrahira, kot je prikazano na zgornjem posnetku zaslona.

Opomba: V formuli CHAR(10) predstavlja prelom vrstice v sistemu Windows.

2.7 Izvlecite besedilo pred ali za besedo

V prejšnjih delih smo se naučili, kako izvleči besedilo pred ali za znakom ali ločilnikom. Kaj morate storiti, da izvlečete besedilo pred ali za celotno besedo? V tem razdelku bodo predstavljeni trije načini, ki vam bodo pomagali pri izpolnjevanju te naloge.

2.7.1 Izvlecite besedilo pred določeno besedo s formulo

Naslednja formula vam pomaga ekstrahirati besedilo pred določeno besedo v celici v Excelu.

Splošna formula

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

Argumenti

Izberite prazno celico, vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

V tem primeru bomo izvlekli vsa besedila pred besedo »Excel«, zato bomo besedo neposredno vnesli v formulo in jo zajeli v dvojne narekovaje. Lahko pa se sklicujete na celico, ki vsebuje besedo »Excel«.

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

Opombe:

2.7.2 Izvlecite besedilo za določeno besedo s formulo

Če želite izvleči besedilo po določeni besedi, lahko uporabite naslednjo formulo, da to storite.

Splošna formula

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

Argumenti

Izberite celico, vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite celico z rezultatom in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

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

Nato lahko vidite vsa besedila po tem, ko je beseda »Excel« v vsaki celici ekstrahirana, kot je prikazano na spodnjem posnetku zaslona.

Opombe:

2.7.3 Izvlecite besedilo pred ali za določeno besedo z neverjetnim orodjem

Če menite, da lahko uporaba formule povzroči veliko nevšečnosti, tukaj toplo priporočamo Izvleček besedila uporabnost Kutools za Excel. Ta funkcija pomaga avtomatizirati nalogo ekstrakcije v Excelu z le nekaj kliki.

1. klik Kutools > Besedilo > Izvleček besedila da omogočite to funkcijo.

2. V Ljubljani Izvleček besedila pogovorno okno, naredite naslednje nastavitve.

Opombe: Če želite ustvariti dinamične rezultate, preverite Vstavi kot formulo škatla. Nato se rezultati samodejno posodobijo, ko se podatki v obsegu spremenijo.

3. Nato an Izvleček besedila Pojavi se pogovorno okno, morate izbrati celico za izpis rezultata in nato klikniti OK gumb.

Nato se besedila pred ali za določeno besedo znotraj vsake celice v izbranem obsegu takoj izvlečejo.

Opomba: Ta funkcija je občutljiva na velike in male črke.

Kliknite, če želite izvedeti več o tej funkciji.

---

3. Izvleček med znaki/besedami

Če želite izluščiti besedilo med določenimi znaki ali besedami, poskusite z naslednjimi metodami.

3.1 Izvlecite besedilo med dvema znakoma

Za ekstrakcijo besedila med dvema znakoma, ki sta lahko enaka ali različna. V tem razdelku je na voljo več načinov, enega od njih pa lahko izberete glede na svoje potrebe.

3.1.1 Izvlecite besedilo med dvema enakima znakoma s formulo

Kot je prikazano na spodnjem posnetku zaslona, ​​​​je v stolpcu B seznam besedilnih nizov in želite iz vsake celice v obsegu izvleči številčni del med znaki »/«, vam lahko naredi uslugo naslednja formula.

Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite celico z rezultatom in jo nato povlecite Ročaj samodejnega izpolnjevanja navzdol, da dobite rezultate drugih celic na seznamu.

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

Nato se iz vsake celice v obsegu izvleče besedilo med dvema enakima znakoma “/”. Glej posnetek zaslona:

Opomba:

3.1.2 Izvlecite besedilo med dvema različnima znakoma s formulo

Ko se naučimo ekstrahirati besedilo med dvema istima znakoma v celici, bomo tukaj prikazali formulo za ekstrakcijo besedila med dvema različnima znakoma. Kot je prikazano na spodnjem posnetku zaslona, ​​lahko iz vsake celice v stolpcu B izvlečete samo e-poštni naslov med »<« in »>«, lahko storite naslednje.

Splošna formula

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

Argumenti

Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

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

Vidite lahko, da je izvlečeno samo besedilo med navedenimi znaki, kot je prikazano na zgornjem posnetku zaslona.

3.1.3 Izvlecite besedilo med dvema znakoma z neverjetnim orodjem

Tukaj zelo priporočamo Izvleči nize med določenim besedilom značilnost Kutools za Excel ki vam pomaga enostavno ekstrahirati besedilo med dvema enakima ali različnima znakoma znotraj celice v Excelu.

1. Izberite prazno celico, da izpišete rezultat, in kliknite Kutools > Pomočnik za formulo > Pomočnik za formulo.

2. V Ljubljani Pomočnik za formule pogovorno okno, naredite naslednje nastavitve.

3. Nato se izvleče samo besedilo med “<” in “>” znotraj celice B5. Medtem je bila ustvarjena formula, lahko izberete to rezultatno celico in nato povlečete njen ročaj za samodejno izpolnjevanje navzdol, da izvlečete besedila iz drugih celic na istem seznamu.

Kliknite, če želite izvedeti več o tej funkciji.

3.1.4 Izvlecite besedilo med dvema znakoma (vključno z znaki) po pravilu

Če želite obdržati oba znaka po ekstrakciji, poskusite uporabiti pravilo v funkciji Ekstrahiranje besedila v Kutools za Excel.

1. klik Kutools > Besedilo > Izvleček besedila.

2. V Ljubljani Izvleček besedila pogovorno okno, naredite naslednje nastavitve.

3. Še en Izvleček besedila Pojavi se pogovorno okno, izberite celico za izpis rezultata in kliknite OK gumb.

Nato se besedilo med določenimi znaki (vključno z znaki) izvleče iz vsake celice v izbranem obsegu v velikem obsegu.

3.2 Izvlecite besedilo med dvema besedama

Poleg ekstrahiranja besedila med dvema znakoma boste morda morali izvleči tudi besedilo med dvema besedama. Na primer, iz vsake celice v stolpcu B izvlecite vse besedilne nize med dvema besedama »KTE« in »feature«, kot je prikazano na spodnjem posnetku zaslona. Za to lahko poskusite z eno od naslednjih metod.

3.2.1 Izvlecite besedilo med dvema besedama s formulo

Za ekstrakcijo vseh besedilnih nizov med dvema besedama znotraj celice lahko uporabite formulo, ki temelji na funkciji MID in funkciji SEARCH.

Splošna formula

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

Argumenti

Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

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

Opombe: V formuli številka 3 predstavlja dolžino znakov besede "KTE"; številka 4 predstavlja dolžino znakov besede "KTE" plus 1.

Vidite lahko, da so vsi besedilni nizi med navedenima besedama izvlečeni iz vsake celice v stolpcu B.

3.2.2 Izvlecite besedilo med dvema besedama z neverjetnim orodjem

Za mnoge uporabnike Excela si je formule težko zapomniti in jih obdelati. Tukaj, z Izvleči nize med določenim besedilom značilnost Kutools za Excel, lahko preprosto izvlečete besedilo med dvema besedama z le nekaj kliki.

1. Izberite celico za izpis rezultata in kliknite Kutools > Pomočnik za formulo > Pomočnik za formulo.

2. V Ljubljani Pomočnik za formulo pogovorno okno, morate konfigurirati na naslednji način.

3. Nato se izvlečejo vsi besedilni nizi med dvema besedama »KTE« in »feature« v celici B5. Medtem je bila ustvarjena formula, lahko izberete to rezultatno celico in nato povlečete njen ročaj za samodejno izpolnjevanje navzdol, da izvlečete besedila iz drugih celic na istem seznamu.

---

Izvlecite številke po položaju

Za seznam alfanumeričnih nizov so lahko trije primeri:

1. Številka je na začetku besedila;
2. Številka je na koncu besedila;
3. Številka je lahko kjerkoli v besedilu.

V tem razdelku bomo zagotovili različne metode, ki jih je mogoče uporabiti za pridobivanje številk v vsakem zgoraj navedenem primeru.

1 Izvlecite številko z leve strani niza

Ta del bo predstavil formulo, ki vam bo pomagala izluščiti samo številke, ki se pojavijo pred besedilom v celici.

Splošna formula

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

Argumenti

Opomba:

Izberite prazno celico, vnesite spodnjo formulo in pritisnite Ctrl + Shift + Vnesite or Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite število drugih celic.

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

Opombe:

---

2 Izvlecite številko z desne strani niza

Kot je prikazano na spodnjem posnetku zaslona, ​​​​če želite izvleči samo številke, ki se pojavijo za besedilom v celici, poskusite z naslednjo formulo.

Splošna formula

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

Argumenti

Opomba:

Izberite prazno celico, vnesite spodnjo formulo in pritisnite Ctrl + Shift + Vnesite or Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite število drugih celic.

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

Opombe:

---

3. Izvlecite vse številke s katerega koli mesta v besedilnem nizu

Zgornje metode pomagajo izluščiti številko samo z leve ali desne strani besedilnega niza. Če želite izvleči vse številke od koder koli v besedilnem nizu, vam ponujamo tri načine, da to storite.

3.1 Izvlecite vsa števila od koder koli v nizu s formulo

Spodnjo formulo lahko uporabite za ekstrakcijo vseh številk od koder koli v besedilnem nizu v Excelu.

1. Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite tipko za pridobivanje vseh številk iz celice 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. Izberite celico z rezultatom in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite vsa števila drugih celic.

3.2 Izvlecite vse številke od koder koli v nizu z VBA

Zgornja formula je predolga in zapletena za številne uporabnike Excela. Pravzaprav lahko zaženete skript VBA za avtomatizacijo opravila v Excelu. To lahko storite na naslednji način.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Na odprtju Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul. Nato kopirajte spodnji VBA v okno kode modula.

Koda VBA: ekstrahirajte vse številke od koder koli v besedilnem nizu

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. Pritisnite F5 tipko za zagon kode. V uvodu KutoolsforExcel v pogovornem oknu izberite obseg celic, kjer želite iz vsake celice izvleči vse številke, in kliknite OK gumb.

4. Potem še eno KutoolsforExcel se prikaže pogovorno okno. V tem pogovornem oknu izberite ciljno celico in kliknite OK.

Nato se iz vsake celice v izbranem obsegu izvlečejo vse številke v velikem obsegu.

---

4. Izvlecite številke po določenem besedilu

Kot je prikazano na spodnjem posnetku zaslona, ​​da izvlečete poljubne številke za določenim besedilom »Št.«, ta razdelek ponuja dva načina, ki vam pomagata pri tem.

4.1 Izvlecite številke po določenem besedilu s formulo

Naslednjo formulo lahko uporabite za ekstrakcijo številk po določenem besedilu v celici v Excelu.

Splošna formula:

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

Argumenti

Izberite prazno celico, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ do rezultata. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

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

Opombe:

4.2 Izvlecite številke po določenem besedilu z uporabniško definirano funkcijo

Naslednja uporabniško definirana funkcija lahko pomaga tudi pri ekstrakciji številk po določenem besedilu v celici. Prosimo, naredite naslednje.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul, in nato spodnjo kodo VBA kopirajte v okno kode modula.

Koda VBA: ekstrahirajte številke po določenem besedilu v celici

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. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Izberite celico, vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite to rezultatno celico in nato povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite to formulo za druge celice.

=GetNumberAfterTheChar(B5,"No. ")

Opombe:

---

Sorodni članki: