Cum să eliminați textul înainte sau după un caracter specific în Excel

Când lucrați cu date text în Excel, este posibil să aveți nevoie să eliminați textul înainte sau după un anumit caracter sau șir de text.

De exemplu, dacă aveți numele și datele de desemnare ale persoanelor, poate doriți să eliminați desemnarea după virgulă și să păstrați numele numai (sau invers unde păstrați desemnarea și eliminați numele).

Uneori, se poate face cu o formulă simplă sau cu o căutare și înlocuire rapidă și, uneori, are nevoie de formule sau soluții mai complexe.

În acest tutorial, vă voi arăta cum să eliminați textul înainte sau după un anumit caracter în Excel (folosind diferite exemple).

Deci, să începem cu câteva exemple simple.

Eliminați textul după un caracter folosind Găsiți și înlocuiți

Dacă doriți să eliminați rapid tot textul după un anumit șir de text (sau înainte de un șir de text), puteți face acest lucru folosind Găsire și înlocuire și caractere wild card.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să eliminați denumirea după caracterul virgulă și să păstrați textul înainte de virgulă.

Mai jos sunt pașii pentru a face acest lucru:

1. Copiați și lipiți datele din coloana A în coloana B (pentru a păstra și datele originale)
2. Cu celulele din coloana B selectate, faceți clic pe fila Acasă
3. În grupul Editare, faceți clic pe opțiunea Găsiți și selectați
4. În opțiunile care apar în meniul derulant, faceți clic pe opțiunea Înlocuire. Aceasta va deschide caseta de dialog Căutare și înlocuire
5. În câmpul „Găsiți ce”, introduceți ,* (adică virgulă urmată de un semn cu asterisc)
6. Lăsați câmpul „Înlocuiți cu” gol
7. Faceți clic pe butonul Înlocuiți toate

Pașii de mai sus ar găsi virgula în setul de date și ar elimina tot textul după virgulă (inclusiv virgula).

Deoarece acest lucru înlocuiește textul din celulele selectate, este o idee bună să copiați textul într-o altă coloană și apoi să efectuați această operațiune de căutare și înlocuire sau să creați o copie de rezervă a datelor dvs., astfel încât să aveți datele originale intacte

Cum funcționează asta?

* (semn asterisc) este un caracter wildcard care poate reprezenta orice număr de caractere.

Când îl folosesc după virgulă (în câmpul „Găsiți ce”) și apoi fac clic pe butonul Înlocuiește totul, găsește prima virgulă din celulă și o consideră potrivită.

Acest lucru se datorează faptului că semnul asterisc (*) este considerat o potrivire cu întregul șir de text care urmează virgulei.

Deci, atunci când apăsați butonul Înlocuiește tot, acesta înlocuiește virgula și tot textul care urmează.

Notă: Această metodă funcționează bine, atunci aveți doar o virgulă în fiecare celulă (ca în exemplul nostru). În cazul în care aveți mai multe virgule, această metodă ar găsi întotdeauna prima virgulă și apoi va elimina totul după ea. Deci, nu puteți utiliza această metodă dacă doriți să înlocuiți tot textul după a doua virgulă și să păstrați primul ca atare.

În cazul în care doriți să eliminați toate caracterele dinaintea virgulei, schimbați intrarea în câmpul Găsiți, având semnul asterisc înaintea virgulei (*, în loc de, *)

Eliminați textul folosind formule

Dacă aveți nevoie de mai mult control asupra modului de a găsi și a înlocui textul înainte sau după un anumit caracter, este mai bine să utilizați formulele de text încorporate în Excel.

Să presupunem că aveți setul de date de mai jos unde doriți să eliminați tot textul după virgulă.

Mai jos este formula pentru a face acest lucru:

= STÂNGA (A2, FIND (",", A2) -1)

Formula de mai sus folosește funcția FIND pentru a găsi poziția virgulei în celulă.

Acest număr de poziție este apoi utilizat de funcția STÂNGA pentru a extrage toate caracterele dinaintea virgulei. Deoarece nu vreau virgulă ca parte a rezultatului, am scăzut 1 din valoarea rezultată a formulei Găsiți.

Acesta a fost un scenariu simplu.

Să luăm una ușor complexă.

Să presupunem că am acest set de date de mai jos, unde vreau să elimin tot textul după a doua virgulă.

Iată formula care va face acest lucru:

= STÂNGA (A2, FIND ("!", SUBSTITUT (A2, ",", "!", 2)) - 1)

Deoarece acest set de date are mai multe virgule, nu pot folosi funcția FIND pentru a obține poziția primei virgule și a extrage totul în stânga acesteia.

Trebuie să aflu cumva poziția celei de-a doua virgule și apoi să extrag tot ce este în stânga celei de-a doua virgule.

Pentru a face acest lucru, am folosit funcția SUBSTITUTE pentru a schimba a doua virgulă într-un semn de exclamare. Acum, acest lucru îmi oferă un personaj unic în celulă. Acum pot folosi poziția semnului exclamării pentru a extrage totul în stânga celei de-a doua virgule.

Această poziție a semnului exclamării este utilizată în funcția STÂNGA pentru a extrage totul înainte de a doua virgulă.

Până acum, bine!

Dar ce se întâmplă dacă există un număr inconsecvent de virgule în setul de date.

De exemplu, în setul de date de mai jos, unele celule au două virgule, iar unele celule au trei virgule și trebuie să extrag tot textul înainte de ultima virgulă.

În acest caz, trebuie să-mi dau seama cumva poziția ultimei apariții a virgulei și apoi să extrag totul în stânga acesteia.

Mai jos este formula care va face asta

= STÂNGA (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))) - 1)

Formula de mai sus folosește funcția LEN pentru a găsi lungimea totală a textului din celulă, precum și lungimea textului fără virgulă.

Când scăd aceste două valori, îmi dă numărul total de virgule din celulă.

Deci, mi-ar da 3 pentru celula A2 și 2 pentru celula A4.

Această valoare este apoi utilizată în formula SUBSTITUTE pentru a înlocui ultima virgulă cu un semn de exclamare. Și apoi puteți utiliza funcția din stânga extrageți tot ce este în stânga semnului exclamării (care a fost ultima virgulă)

După cum puteți vedea în exemple, utilizarea unei combinații de formule de text vă permite să gestionați multe situații diferite.

De asemenea, deoarece rezultatul este legat de datele originale, atunci când modificați datele originale, rezultatul se va actualiza automat.

Eliminați textul utilizând Flash Fill

Flash Fill este un instrument care a fost introdus în Excel 2013 și este disponibil în toate versiunile după aceea.

Funcționează identificând modele atunci când introduceți manual datele și apoi extrapolați pentru a vă oferi datele pentru întreaga coloană.

Deci, dacă doriți să eliminați textul înainte sau după un anumit caracter, trebuie doar să arătați zână flash cum ar arăta rezultatul (prin introducerea manuală de câteva ori), iar umplerea blițului ar înțelege automat modelul și vă va oferi rezultatele.

Permiteți-mi să vă arăt acest lucru cu un exemplu.

Mai jos am setul de date în care vreau să elimin tot textul după virgulă.

Iată pașii pentru a face acest lucru folosind Flash Fill:

1. În celula B2, care este coloana adiacentă a datelor noastre, introduceți manual „Jeffery Haggins” (care este rezultatul așteptat)
2. În celula B3, introduceți „Tim Scott” (care este rezultatul așteptat pentru a doua celulă)
3. Selectați intervalul B2: B10
4. Faceți clic pe fila Acasă
5. În grupul Editare, faceți clic pe opțiunea derulantă Umplere
6. Faceți clic pe Flash Fill

Pașii de mai sus vă vor oferi rezultatul așa cum se arată mai jos:

De asemenea, puteți utiliza comanda rapidă de la tastatură Flash Fill Control + E după selectarea celulelor din coloana de rezultate (Coloana B din exemplul nostru)

Flash Fill este un instrument minunat și, în majoritatea cazurilor, este capabil să recunoască modelul și să vă ofere rezultatul corect. dar, în unele cazuri, este posibil să nu poată recunoaște corect modelul și să vă dea rezultate greșite.

Deci, asigurați-vă că verificați din nou rezultatele Flash Fill

Și la fel cum am eliminat tot textul după un anumit caracter folosind umplerea cu bliț, puteți utiliza aceiași pași pentru a elimina textul înainte de un anumit caracter. Arată doar umplerea cu bliț, cum ar trebui ca rezultatul să arate ca Internetul meu manual în coloana alăturată, iar acesta ar face restul.

Eliminați textul utilizând VBA (funcție personalizată)

Întregul concept de eliminare a textului înainte sau după un caracter specific este dependent de găsirea poziției acelui caracter.

Așa cum s-ar vedea mai sus, găsirea bună a ultimei apariții a acelui caracter înseamnă utilizarea unui amestec de formule.

Dacă trebuie să faceți acest lucru destul de des, puteți simplifica acest proces prin crearea unei funcții personalizate utilizând VBA (numite funcții definite de utilizator).

Odată creată, puteți reutiliza această funcție din nou și din nou. De asemenea, este mult mai simplu și mai ușor de utilizat (deoarece cea mai mare parte a ridicării grele se face prin codul VBA din partea din spate).

Sub codul VBA pe care îl puteți utiliza pentru a crea funcția personalizată în Excel:

Funcția LastPosition (rCell As Range, rChar As String) „Această funcție oferă ultima poziție a caracterului specificat” Acest cod a fost dezvoltat de Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Pentru i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Exit Function End End If Next i End Function

Trebuie să plasați codul VBA într-un modul obișnuit în Editorul VB sau în registrul de lucru personal Macro. După ce îl aveți acolo, îl puteți folosi ca orice altă funcție obișnuită a foii de lucru din registrul de lucru.

Această funcție are 2 argumente:

1. Referința celulei pentru care doriți să găsiți ultima apariție a unui anumit caracter sau șir de text
2. Caracterul sau șirul de text a cărui poziție trebuie să o găsiți

Să presupunem că aveți acum setul de date de mai jos și doriți să eliminați tot textul după ultima virgulă și să aveți doar textul înainte de ultima virgulă.

Mai jos este formula care va face acest lucru:

= STÂNGA (A2, UltimaPoziție (A2, ",") - 1)

În formula de mai sus, am specificat pentru a găsi poziția ultimei virgule. În cazul în care doriți să găsiți poziția unui alt caracter sau șir de text, ar trebui să îl utilizați ca al doilea argument din funcție.

După cum puteți vedea, acest lucru este mult mai scurt și mai ușor de utilizat, în comparație cu formula de text lung pe care am folosit-o în secțiunea anterioară

Dacă introduceți codul VBA într-un modul dintr-un registru de lucru, veți putea utiliza această funcție personalizată numai în acel registru de lucru specific. Dacă doriți să utilizați acest lucru în toate registrele de lucru de pe sistemul dvs., trebuie să copiați și să inserați acest cod în registrul personal de lucru Macro.

Deci, acestea sunt câteva dintre mesajele de poștă electronică pe care le puteți utiliza pentru a elimina rapid textul înainte sau după un anumit caracter în Excel.

Dacă este o singură dată, puteți face acest lucru folosind găsiți și înlocuiți. Ce se întâmplă dacă este puțin mai complex, atunci trebuie să utilizați o combinație de formule Excel încorporate sau chiar să vă creați propria formulă personalizată utilizând VBA.

Sper că ți s-a părut util acest tutorial.