O problemă simplă, la prima vedere, cu o soluție neevidentă: extrage ultimul cuvânt dintr-o linie de text. Ei bine, sau, în cazul general, ultimul fragment, separat printr-un caracter delimitator dat (spațiu, virgulă etc.) Cu alte cuvinte, este necesar să se implementeze o căutare inversă (de la capăt la început) în șirul unui caracterul dat și apoi extrageți toate caracterele din dreapta acestuia.
Să ne uităm la mai multe moduri tradiționale de a alege: formule, macrocomenzi și prin Power Query.
Metoda 1. Formule
Pentru a înțelege mai ușor esența și mecanica formulei, să începem puțin de departe. Mai întâi, să creștem numărul de spații dintre cuvintele din textul nostru sursă la, de exemplu, 20 de bucăți. Puteți face acest lucru cu funcția de înlocuire. SUBSTITUI (SUBSTITUI) și funcția de repetare a unui caracter dat de N-ori – REPETA (REPT):
Acum tăiem 20 de caractere de la sfârșitul textului rezultat folosind funcția DREAPTA (DREAPTA):
Se încălzește, nu? Rămâne să eliminați spațiile suplimentare folosind funcția TUNDE (TUNDE) si problema va fi rezolvata:
În versiunea în limba engleză, formula noastră va arăta astfel:
=TRIM(DREAPTA(ÎNLOCUIT(A1;» «;REPT(» «;20));20))
Sper că este clar că, în principiu, nu este necesar să introduceți exact 20 de spații – orice număr va face, atâta timp cât este mai mare decât lungimea celui mai lung cuvânt din textul sursă.
Și dacă textul sursă trebuie împărțit nu printr-un spațiu, ci printr-un alt caracter separator (de exemplu, printr-o virgulă), atunci formula noastră va trebui să fie ușor corectată:
Metoda 2. Funcția macro
Sarcina de a extrage ultimul cuvânt sau fragment din text poate fi rezolvată și folosind macrocomenzi, și anume, scrierea unei funcții de căutare inversă în Visual Basic care va face ceea ce ne trebuie – căutarea unui subșir dat într-un șir în direcția opusă – din sfârșitul până la început.
Apăsați comanda rapidă de la tastatură Alt+F11 sau butonul Visual Basic fila dezvoltator (Dezvoltator)pentru a deschide editorul de macrocomenzi. Apoi adăugați un nou modul prin meniu Inserare – Modul și copiați următorul cod acolo:
Funcția LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) Funcție End
Acum puteți salva registrul de lucru (într-un format cu macro-activat!) și puteți utiliza funcția creată în următoarea sintaxă:
=LastWord(txt; delim; n)
Unde
- txt – celula cu text sursă
- delim — caracter separator (implicit — spațiu)
- n – ce cuvânt ar trebui extras de la sfârșit (implicit – primul de la sfârșit)
Cu orice modificări ale textului sursă în viitor, funcția noastră macro va fi recalculată din mers, ca orice funcție standard de foaie Excel.
Metoda 3. Power Query
Interogare de alimentare este un add-on gratuit de la Microsoft pentru importarea datelor în Excel din aproape orice sursă și apoi transformarea datelor descărcate în orice formă. Puterea și răceala acestui program de completare sunt atât de mari încât Microsoft și-a integrat toate caracteristicile în Excel 2016 în mod implicit. Pentru Excel 2010-2013 Power Query poate fi descărcat gratuit de aici.
Sarcina noastră de a separa ultimul cuvânt sau fragment printr-un anumit separator folosind Power Query este rezolvată foarte ușor.
Mai întâi, să transformăm tabelul nostru de date într-un tabel inteligent folosind comenzile rapide de la tastatură. Ctrl+T sau comenzi Acasă – Formatați ca tabel (Acasă — Format ca tabel):
Apoi încărcăm „tabelul inteligent” creat în Power Query folosind comanda De la masă/gamă (De la masă/gamă) fila Date (dacă aveți Excel 2016) sau pe filă Interogare de alimentare (dacă aveți Excel 2010-2013):
În fereastra editorului de interogări care se deschide, pe fila Transformare (Transforma) alege o echipa Coloana împărțită – După delimitare (Divizați coloana — După delimitator) și apoi rămâne să setați caracterul separator și să selectați opțiunea Delimitator din dreaptaa tăia nu toate cuvintele, ci doar pe ultimul:
După ce faceți clic pe OK ultimul cuvânt va fi separat într-o nouă coloană. Prima coloană inutilă poate fi eliminată făcând clic dreapta pe antetul acesteia și selectând Elimină (Șterge). De asemenea, puteți redenumi coloana rămasă din antetul tabelului.
Rezultatele pot fi încărcate înapoi în foaie folosind comanda Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închideți și încărcați — Închideți și încărcați în...):
Și ca rezultat obținem:
Așa – ieftin și vesel, fără formule și macro-uri, aproape fără a atinge tastatura 🙂
Dacă lista originală se modifică în viitor, va fi suficient să faceți clic dreapta sau să utilizați o comandă rapidă de la tastatură Ctrl+Alt+F5 actualizați cererea noastră.
- Împărțirea textului lipicios în coloane
- Analizarea și analizarea textului cu expresii regulate
- Extragerea primelor cuvinte din text cu funcția SUBSTITUTE