Tutorial Excel – extrageți text sau număr după poziție specifică

În multe cazuri, este posibil să fie nevoie doar să extrageți conținutul util dintr-o propoziție sau dintr-un șir de text dintr-o celulă, cum ar fi extragerea provinciei din adresă, extragerea adresei de e-mail dintr-o propoziție, extragerea numărului facturii de curier dintr-o conversație, și așa mai departe. Acest tutorial restrânge extragerea la o anumită poziție într-o celulă și colectează diferite metode pentru a ajuta la extragerea textului sau a numerelor dintr-o celulă după o anumită poziție în Excel.

---

Extrageți textul după poziție

Această secțiune adună poziții comune în care un text poate fi extras dintr-o celulă și oferă metode corespunzătoare pentru a le gestiona pas cu pas. Puteți naviga pentru mai multe detalii.

1. Extrageți numărul de caractere din stânga sau din dreapta

Pentru a extrage numărul de caractere din partea stângă sau dreaptă a unui șir, puteți încerca una dintre metodele de mai jos.

1.1 Extrageți primul sau ultimul N caractere cu formule

Presupunând că aveți o listă de șiruri de text în coloana B, așa cum se arată în captura de ecran de mai jos, pentru a extrage primele 2 caractere și ultimele 2 caractere din fiecare șir, puteți aplica următoarele formule.

Extrageți primele N caractere dintr-un șir de text

Funcția LEFT poate ajuta la extragerea cu ușurință a primelor N caractere dintr-un șir de text în Excel.

Formula generică

=LEFT(text_string,[num_chars])

Argumente

Acum puteți aplica această formulă pentru a extrage primele 2 caractere din celulele din coloana B.

1. Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține primul rezultat. Selectați celula rezultat și trageți în jos mânerul de completare automată pentru a aplica formula altor celule.

=LEFT(B5,2)

Acum au fost extrase primele 2 caractere din fiecare celulă din intervalul B5:B10.

Extrage ultimele N caractere dintr-un șir de text

Aici aplicăm funcția RIGHT pentru a extrage ultimele N caractere dintr-un șir de text în Excel.

Formula generică

=RIGHT(text_string,[num_chars])

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Apoi selectați această celulă de rezultat și trageți în jos mânerul de completare automată pentru a obține alte rezultate.

=RIGHT(B5,2)

1.2 Extrageți primul sau ultimul N caractere cu un instrument uimitor

Deși formulele de mai sus sunt simple, pentru a extrage primele sau ultimele n caractere dintr-o listă lungă de șir de text, trebuie totuși să trageți de sus în jos mânerul de completare automată, ceea ce poate consuma puțin timp. Aici recomandă Kutools pentru Excel'S Extrageți textul utilitar pentru a ajuta la extragerea primului sau ultimului N caractere dintr-o listă de șiruri de text în bloc.

1. Selectați lista de șiruri de text din care doriți să extrageți textul în avans și faceți clic Kutools > Text > Extrageți textul.

2. În fereastra pop-up Extrageți textul casetă de dialog, trebuie să configurați după cum urmează.

notițe: Pentru a face rezultatul dinamic atunci când șirul de text se modifică, puteți verifica Inserați ca formulă cutie.

3. În următoarea fereastră pop-up Extrageți textul caseta de dialog, alegeți o celulă pentru a scoate caracterele extrase și apoi faceți clic OK.

Apoi, primele sau ultimele N caractere specificate sunt extrase din celulele selectate în bloc.

Faceți clic pentru a afla mai multe despre această caracteristică.

---

2. Extrageți text înainte sau după un anumit caracter/cuvânt

Pentru a extrage text înainte sau după un anumit caracter sau cuvânt, diferitele scenarii din această secțiune vă vor satisface nevoile.

2.1 Extrageți text înainte sau după primul delimitator (un caracter)

După cum se arată în captura de ecran de mai jos, pentru a extrage text înainte sau după primul delimitator din fiecare celulă din intervalul B4:B10, puteți aplica una dintre metodele de mai jos.

2.1.1 Extrageți text înaintea primului delimitator cu formulă

Aplicarea unei formule bazate pe funcțiile LEFT și FIND poate ajuta la extragerea textului înainte de primul delimitator dintr-o celulă. Puteți urma pașii de mai jos pentru a finaliza.

Formula generică

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

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos în ea și apăsați tasta Intrați cheie pentru a obține primul rezultat. Selectați prima celulă rezultat și trageți în jos mânerul de completare automată pentru a obține textele înaintea primului delimitator al altor celule.

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

2.1.2 Extrageți text după primul delimitator cu formulă

Formula de mai jos ajută la extragerea textului după primul delimitator dintr-o celulă în Excel.

Formula generică

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

Argumente

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

2.1.3 Extrageți text înainte sau după primul delimitator cu un instrument uimitor

Aici recomandă cu căldură Extrageți textul utilitatea Kutools pentru Excel. Cu această caracteristică, puteți extrage cu ușurință texte înainte sau după primul delimitator dintr-o serie de celule în bloc.

1. Selectați intervalul de celule în care doriți să extrageți textul, apoi faceți clic Kutools > Text > Extrageți textul.

2. În Extrageți textul casetă de dialog, trebuie să configurați după cum urmează.

Notă: Pentru a face rezultatul dinamic atunci când șirul de text se modifică, puteți bifa caseta Inserare ca formulă.

3. Apoi alta Extrageți textul apare caseta de dialog, alegeți o celulă pentru a afișa rezultatele și faceți clic OK.

Apoi, textele înainte sau după primul delimitator sunt extrase dintr-o dată din celulele selectate.

Pentru a afla mai multe despre această funcție, vă rugăm să vizitați: Extrageți rapid un anumit text din celule în Excel.

2.2 Extrageți text înainte sau după ultimul delimitator (un caracter)

În pașii de mai sus am învățat metodele despre extragerea textului înainte sau după primul delimitator dintr-o celulă. După cum se arată în captura de ecran de mai jos, această secțiune vă va arăta două formule pentru a extrage text înainte sau după ultimul delimitator dintr-o celulă. Puteți urma pașii de mai jos pentru a finaliza.

2.2.1 Extrageți text înainte de ultimul delimitator cu formulă

Pentru a extrage text înainte de ultimul delimitator dintr-o celulă, puteți utiliza funcțiile SEARCH, LEN și SUBSTITUTE din cadrul funcției LEFT.

Formula generică

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

Argumente

Selectați o celulă, introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă de rezultat și trageți în jos mânerul de completare automată pentru a extrage textele din alte șiruri de text din aceeași coloană.

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

2.2.2 Extrageți text după ultimul delimitator cu formulă

După extragerea textului înainte de ultimul delimitator dintr-o celulă, puteți aplica formula de mai jos pentru a extrage textul după ultimul delimitator după cum aveți nevoie.

Formula generică

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

Argumente

Selectați o celulă, introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă de rezultat și trageți în jos mânerul de completare automată pentru a extrage textele din alte șiruri de text din aceeași coloană.

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

2.3 Extrageți text după al n-lea caracter

Priviți exemplul de mai jos, există o listă de șiruri de text în intervalul B4:B10, pentru a extrage textul după al treilea caracter din fiecare celulă, puteți aplica o formulă bazată pe funcția MID și funcția LEN.

Formula generică

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

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos în ea și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă de rezultat și trageți în jos mânerul de completare automată pentru a obține alte rezultate.

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

2.4 Extrageți al n-lea cuvânt dintr-un șir de text

Presupunând că aveți o listă de șiruri de text așa cum se arată în captura de ecran de mai jos și doriți să extrageți doar al n-lea cuvânt din șirul de text, această secțiune vă oferă trei metode pentru a le realiza.

2.4.1 Extrageți al n-lea cuvânt cu formula

Puteți combina funcțiile TRIM, MID, SUBSTITUTE, REPT și LEN pentru a extrage al-lea cuvânt dintr-un șir de text dintr-o celulă.

Formula generică

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

Argumente

În acest caz, intervalul B5:B10 conține șirurile de text, D5:D10 conține numerele care reprezintă al n-lea cuvânt, să aplicăm această formulă pentru a extrage al n-lea cuvânt din șirul de text.

Selectați o celulă goală, copiați sau introduceți formula de mai jos în ea și apăsați tasta Intrați cheie pentru a obține primul rezultat. Selectați această celulă rezultat și trageți în jos mânerul de completare automată pentru a obține al n-lea cuvânt al altor celule.

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

Notă: Puteți introduce direct al n-lea număr în formulă, după cum urmează.

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

2.4.2 Extrageți al n-lea cuvânt cu funcția definită de utilizator

În afară de formula de mai sus, puteți aplica și o funcție definită de utilizator pentru a extrage al-lea cuvânt dintr-o celulă în Excel.

1. apasă pe Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. În Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Modul, și apoi copiați VBA de mai jos în fereastra Cod.

Cod VBA: extrageți al n-lea cuvânt dintr-un șir de text dintr-o celulă

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. apasă pe Alt + Q tastele pentru a închide Microsoft Visual Basic pentru aplicații fereastră.

4. Reveniți la foaia de lucru care conține șirurile de text din care doriți să extrageți al n-lea cuvânt. Selectați o celulă goală, copiați sau introduceți formula de mai jos în ea și apăsați tasta Intrați cheie pentru a obține al n-lea cuvânt.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Notă: în formulă, D5 este celula care conține un număr care reprezintă al n-lea cuvânt. Alternativ, puteți înlocui direct referința celulei cu un număr.

5. Selectați celula rezultat și trageți în jos mânerul de completare automată pentru a extrage al n-lea cuvânt din șirurile de text ale altor celule.

2.4.3 Extrageți al n-lea cuvânt cu un instrument uimitor

Dacă nu doriți să aplicați manual o formulă sau o funcție definită de utilizator furnizată mai sus, aici vă recomandă Kutools pentru Excel Extrageți al doilea cuvânt în celulă utilitate. Cu această caracteristică, puteți extrage cu ușurință al n-lea cuvânt dintr-un șir de text dintr-o celulă doar cu câteva clicuri.

1. Selectați o celulă pentru a plasa rezultatul și faceți clic Kutools > Formula Helper > Text > Extrage al n-lea cuvânt din celulă. Vedeți captura de ecran:

2. În Ajutor pentru formule casetă de dialog, trebuie să configurați după cum urmează.

3. Apoi, al n-lea (al doilea) cuvânt este extras din șirul de text din celula B5 și puteți vedea că o formulă este creată în același timp. Selectați această celulă de rezultat și trageți în jos mânerul de completare automată pentru a obține al-lea cuvânt din alte șiruri de text.

Faceți clic pentru a afla mai multe despre această caracteristică.

2.5 Extrageți text înainte sau după a n-a apariție a unui delimitator

Să presupunem că aveți o listă de șiruri de text, așa cum se arată în captura de ecran de mai jos. Pentru a extrage textul înainte sau după a doua apariție a unui spațiu, această secțiune oferă două formule pentru a vă ajuta să finalizați.

2.5.1 Extrageți text înainte de a n-a apariție a unui delimitator

Puteți utiliza funcția LEFT împreună cu funcțiile SUBSTITUTE și FIND pentru a extrage text înaintea apariției a n-a a unui delimitator dintr-o celulă în Excel.

Formula generică

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

Argumente

Selectați o celulă, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă de rezultat și trageți-o în jos pentru a obține alte rezultate în listă.

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

Notă: În formulă, B5 este celula care conține șirul de text din care doriți să extrageți textul; „ ” aici reprezintă un spațiu, iar numărul 2 reprezintă a doua apariție a unui spațiu. Le poți schimba în funcție de nevoile tale.

2.5.2 Extrageți text după a n-a apariție a unui delimitator

Pentru a extrage text după a n-a apariție a unui delimitator, puteți aplica funcția DREAPTA cu funcțiile SUBSTITUTE, LEN și FIND.

Formula generică

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

Argumente

Acum puteți aplica această formulă pentru a extrage textul după a doua apariție a spațiului din fiecare celulă din intervalul B5: B10, după cum urmează.

Selectați o celulă, introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă de rezultat și trageți-o în jos pentru a obține alte rezultate.

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

2.6 Extrageți text înainte sau după întrerupere de linie

Să presupunem că aveți o listă de comandă în coloana B și doriți să extrageți din fiecare celulă doar partea de dată și partea nr. de produs. Puteți utiliza formulele Excel de mai jos pentru a face acest lucru.

2.6.1 Extrageți text înainte de prima întrerupere de linie cu formula

După cum puteți vedea în captura de ecran de mai sus, partea de dată se localizează înainte de prima întrerupere de linie din celulă. Această secțiune va demonstra o funcție LEFT împreună cu funcția SEARCH pentru a vă ajuta să extrageți textul înainte de prima întrerupere de linie dintr-o celulă.

Formula generică

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

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos în ea și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a aplica această formulă altor celule.

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

Apoi puteți vedea textul înainte ca prima întrerupere de linie din fiecare celulă din intervalul B5:B8 să fie extras, așa cum se arată în captura de ecran de mai jos.

Notă: În formulă, CHAR(10) reprezintă o întrerupere de linie pe Windows.

2.6.2 Extrageți text după ultima întrerupere de linie cu formula

În pasul anterior, am vorbit despre cum să extragem text înainte de prima întrerupere de linie dintr-o celulă. Și această parte vă va ghida cum să extrageți text după ultima întrerupere de linie dintr-o celulă cu o formulă diferită.

Formula generică

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

Argumente

Selectați o celulă goală, introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți în jos mânerul de completare automată pentru a aplica formula altor celule.

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

Apoi produsul nr. o parte din fiecare celulă din listă este extrasă așa cum se arată în captura de ecran de mai sus.

Notă: În formulă, CHAR(10) reprezintă o întrerupere de linie pe Windows.

2.7 Extrageți text înainte sau după un cuvânt

În părțile anterioare, am învățat cum să extragem text înainte sau după un caracter sau delimitator. Ce ar trebui să faceți pentru a extrage text înainte sau după un cuvânt întreg? Această secțiune va prezenta trei metode pentru a vă ajuta să îndepliniți această sarcină.

2.7.1 Extrageți text înaintea unui anumit cuvânt cu formulă

Următoarea formulă vă ajută să extrageți text înaintea unui anumit cuvânt într-o celulă în Excel.

Formula generică

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

Argumente

Selectați o celulă goală, introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a aplica această formulă altor celule.

În acest exemplu, vom extrage toate textele înainte de cuvântul „Excel”, așa că introducem direct cuvântul în formulă și îl închidem între ghilimele duble. Sau puteți face referire la o celulă care conține cuvântul „Excel”.

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

note:

2.7.2 Extrageți text după un anumit cuvânt cu formulă

Pentru a extrage text după un anumit cuvânt, puteți aplica următoarea formulă pentru a finaliza.

Formula generică

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

Argumente

Selectați o celulă, introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați celula rezultat și apoi trageți în jos mânerul de completare automată pentru a aplica această formulă altor celule.

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

Apoi puteți vedea toate textele după ce cuvântul „Excel” din fiecare celulă este extras, așa cum se arată în captura de ecran de mai jos.

note:

2.7.3 Extrageți text înainte sau după un anumit cuvânt cu un instrument uimitor

Dacă simțiți că utilizarea formulei poate cauza multe neplăceri, aici vă recomandăm cu căldură Extrageți textul utilitatea Kutools pentru Excel. Această caracteristică ajută la automatizarea sarcinii de extragere în Excel cu doar câteva clicuri.

1. clic Kutools > Text > Extrageți textul pentru a activa această caracteristică.

2. În Extrageți textul caseta de dialog, efectuați următoarele setări.

note: Dacă doriți să creați rezultate dinamice, verificați Inserați ca formulă cutie. Apoi rezultatele se vor actualiza automat când datele din interval se modifică.

3. Apoi un Extrageți textul apare caseta de dialog, trebuie să selectați o celulă pentru a afișa rezultatul și apoi să faceți clic pe OK butonul.

Apoi textele înainte sau după un anumit cuvânt din fiecare celulă din intervalul selectat sunt extrase imediat.

Notă: Această caracteristică face distincție între majuscule și minuscule.

Faceți clic pentru a afla mai multe despre această caracteristică.

---

3. Extrage între caractere/cuvinte

Dacă doriți să extrageți text între anumite caractere sau cuvinte, încercați următoarele metode.

3.1 Extrageți text între două caractere

Pentru a extrage text între două caractere, care pot fi caractere identice sau diferite. Această secțiune oferă mai multe metode și puteți alege una dintre ele în funcție de nevoile dvs.

3.1.1 Extrageți text între două caractere identice cu formula

După cum se arată în captura de ecran de mai jos, există o listă de șiruri de text în coloana B și doriți să extrageți partea numerică dintre caracterele „/” din fiecare celulă din interval, următoarea formulă vă poate face o favoare.

Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați celula rezultat și apoi trageți-o în jos pentru a obține rezultatele altor celule din listă.

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

Apoi textul dintre două aceleași caractere „/” este extras din fiecare celulă din interval. Vedeți captura de ecran:

Notă:

3.1.2 Extrageți text între două caractere diferite cu formulă

După ce ați învățat cum să extrageți text între două caractere identice dintr-o celulă, aici vom demonstra o formulă pentru a extrage text între două caractere diferite. După cum se arată în captura de ecran de mai jos, pentru a extrage numai adresa de e-mail între „<” și „>” din fiecare celulă din coloana B, puteți face după cum urmează.

Formula generică

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

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a aplica această formulă altor celule.

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

Puteți vedea că numai textul dintre caracterele specificate este extras așa cum se arată în captura de ecran de mai sus.

3.1.3 Extrageți text între două caractere cu un instrument uimitor

Aici recomandă cu căldură Extrageți șiruri între textul specificat caracteristică a Kutools pentru Excel pentru a vă ajuta să extrageți cu ușurință text între două caractere identice sau diferite dintr-o celulă în Excel.

1. Selectați o celulă necompletată pentru a afișa rezultatul, apoi faceți clic pe Kutools > Formula Helper > Formula Helper.

2. În Ajutor pentru formule caseta de dialog, efectuați următoarele setări.

3. Apoi este extras doar textul dintre „<” și „>” din celula B5. Între timp, a fost creată o formulă, puteți selecta această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a extrage texte din alte celule din aceeași listă.

Faceți clic pentru a afla mai multe despre această caracteristică.

3.1.4 Extrageți text între două caractere (inclusiv caracterele) după regulă

Dacă doriți să păstrați cele două caractere după extragere, încercați să aplicați o regulă în caracteristica Extragere text din Kutools pentru Excel.

1. clic Kutools > Text > Extrageți textul.

2. În Extrageți textul caseta de dialog, efectuați următoarele setări.

3. Altul Extrageți textul apare caseta de dialog, selectați o celulă pentru a afișa rezultatul, apoi faceți clic pe OK butonul.

Apoi, textul dintre caracterele specificate (inclusiv caracterele) este extras din fiecare celulă din intervalul selectat în bloc.

3.2 Extrageți text între două cuvinte

Pe lângă extragerea textului dintre două caractere, poate fi necesar să extrageți și text între două cuvinte. De exemplu, extrageți toate șirurile de text dintre două cuvinte „KTE” și „feature” din fiecare celulă din coloana B, așa cum se arată în captura de ecran de mai jos. Puteți încerca una dintre următoarele metode pentru a face acest lucru.

3.2.1 Extrageți text între două cuvinte cu formulă

Puteți utiliza o formulă bazată pe funcția MID și pe funcția SEARCH pentru a extrage toate șirurile de text dintre două cuvinte dintr-o celulă.

Formula generică

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

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a aplica această formulă altor celule.

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

notițe: În formulă, numărul 3 reprezintă lungimea caracterului cuvântului „KTE”; numărul 4 reprezintă lungimea caracterului cuvântului „KTE” plus 1.

Puteți vedea că toate șirurile de text dintre cele două cuvinte specificate sunt extrase din fiecare celulă din coloana B.

3.2.2 Extrageți text între două cuvinte cu un instrument uimitor

Pentru mulți utilizatori Excel, formulele pot fi greu de reținut și de gestionat. Aici, cu Extrageți șiruri între textul specificat caracteristică a Kutools pentru Excel, puteți extrage cu ușurință text între două cuvinte cu doar câteva clicuri.

1. Selectați o celulă pentru a scoate rezultatul, apoi faceți clic Kutools > Formula Helper > Formula Helper.

2. În Formula Helper casetă de dialog, trebuie să configurați după cum urmează.

3. Apoi sunt extrase toate șirurile de text dintre două cuvinte „KTE” și „feature” din celula B5. Între timp, a fost creată o formulă, puteți selecta această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a extrage texte din alte celule din aceeași listă.

---

Extrageți numere după poziție

Pentru o listă de șiruri alfanumerice, pot exista trei cazuri:

1. Numărul este la începutul textului;
2. Numărul este la sfârșitul textului;
3. Numărul poate fi oriunde în text.

În această secțiune, vom oferi diferite metode care pot fi utilizate pentru a extrage numerele în fiecare caz menționat mai sus.

1 Extrageți numărul din stânga unui șir

Această parte va introduce o formulă pentru a vă ajuta să extrageți numai numerele care apar înaintea textului într-o celulă.

Formula generică

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

Argumente

Notă:

Selectați o celulă goală, introduceți formula de mai jos și apăsați Ctrl + Schimba + Intrați or Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți în jos mânerul de completare automată pentru a obține un număr de alte celule.

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

note:

---

2 Extrageți numărul din dreapta unui șir

După cum se arată în captura de ecran de mai jos, pentru a extrage numai numerele care apar după text într-o celulă, încercați următoarea formulă.

Formula generică

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

Argumente

Notă:

Selectați o celulă goală, introduceți formula de mai jos și apăsați Ctrl + Schimba + Intrați or Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți în jos mânerul de completare automată pentru a obține un număr de alte celule.

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

note:

---

3. Extrageți toate numerele din orice poziție dintr-un șir de text

Metodele de mai sus ajută la extragerea numărului numai din stânga sau din dreapta unui șir de text. Dacă doriți să extrageți toate numerele de oriunde dintr-un șir de text, aici vă oferim trei metode pentru a le realiza.

3.1 Extrageți toate numerele de oriunde dintr-un șir cu formula

Puteți aplica următoarea formulă pentru a extrage toate numerele de oriunde dintr-un șir de text în Excel.

1. Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține toate numerele din celula 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. Selectați celula rezultat și apoi trageți în jos mânerul de completare automată pentru a obține toate numărul celorlalte celule.

3.2 Extrageți toate numerele de oriunde dintr-un șir cu VBA

Formula de mai sus este prea lungă și complicată pentru mulți utilizatori Excel. De fapt, puteți rula un script VBA pentru a automatiza sarcina în Excel. Puteți face după cum urmează.

1. apasă pe Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. În deschidere Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Modul. Apoi copiați VBA de mai jos în fereastra Cod modul.

Cod VBA: extrageți toate numerele de oriunde dintr-un șir de text

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. apasă pe F5 tasta pentru a rula codul. În deschidere KutoolsforExcel caseta de dialog, selectați intervalul de celule în care doriți să extrageți toate numerele din fiecare celulă, apoi faceți clic pe OK butonul.

4. Apoi alta KutoolsforExcel apare caseta de dialog. În această casetă de dialog, selectați o celulă de destinație și faceți clic OK.

Apoi, toate numerele sunt extrase din fiecare celulă din intervalul selectat în bloc.

---

4. Extrage numerele după un anumit text

După cum se arată în captura de ecran de mai jos, pentru a extrage orice numere după textul specific „Nu”, această secțiune oferă două metode pentru a vă ajuta să finalizați.

4.1 Extrageți numere după un anumit text cu formulă

Puteți aplica următoarea formulă pentru a extrage numere după un anumit text dintr-o celulă în Excel.

Formula generică:

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

Argumente

Selectați o celulă goală, copiați sau introduceți formula de mai jos și apăsați tasta Intrați cheie pentru a obține rezultatul. Selectați această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a aplica această formulă altor celule.

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

note:

4.2 Extrageți numere după un anumit text cu funcție definită de utilizator

Următoarea funcție definită de utilizator poate ajuta și la extragerea numerelor după un anumit text dintr-o celulă. Vă rugăm să faceți după cum urmează.

1. apasă pe Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. În Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Modul, apoi copiați codul VBA de mai jos în fereastra Cod modul.

Cod VBA: extrage numere după un anumit text dintr-o celulă

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. apasă pe Alt + Q tastele pentru a închide Microsoft Visual Basic pentru aplicații fereastră.

4. Selectați o celulă, introduceți formula de mai jos și apăsați tasta Intrați cheie. Selectați această celulă rezultat și apoi trageți mânerul de completare automată în jos pentru a aplica această formulă altor celule.

=GetNumberAfterTheChar(B5,"No. ")

note:

---

Legate de articole: