1. Ce este concatenarea in Excel?

In esenta, exista doua moduri de a combina datele in foile de calcul Excel:

– Unirea celulelor fizic (merge)

– Alipirea valorilor din mai multe celule (concatenate)

Cand imbinati celulele, „fizic” alipiti doua sau mai multe celule intr-o singura celula. Drept urmare, aveti o celula mai mare care este afisata pe mai multe randuri si / sau coloane din foaia de lucru.

Cand concatenati valorile celulelor in Excel, combinati numai continutul acelor celule. Cu alte cuvinte, concatenarea in Excel este procesul de unire a doua sau mai multor valori impreuna. Aceasta metoda este adesea folosita pentru a combina cateva bucati de text care se afla in diferite celule sau pentru a introduce o valoare calculata cu o formula in mijlocul unui text existent in alta celula.

Urmatoarea captura de ecran demonstreaza diferenta dintre aceste doua metode:

Desigur ca in acest articol o sa ne concentratam pe cea de a doua metoda (concatenere), prima metoda (de unire fizica- merge cells) nu are nimic special si nici nu este recomandata a se folosi.

Metoda 1: folosind operatorul de concatenare ampersand (&)

In Microsoft Excel operatorul & reprezinta cel mai simplu mod de a concatena valorile celulelor.

Aceasta metoda este foarte utila in multe scenarii, deoarece tastarea semnului ampersand (&) este mult mai rapida decat introducerea unei formule ce foloseste functia Concatenate (vezi mai jos)

Puteti utiliza simbolul „&” in Excel pentru a combina diferite siruri de text, valori ale celulelor sau rezultatele returnate de alte functii.

Exemple de formule Excel cu operatorul de concatenare „&”:

Concateneaza valorile din A2 si A1:

= A2 & A1

Concateneaza valorile din A2 si A1 separate cu un spatiu:

= A2 & ” ” & A1

Concateneaza valorile din A2, B1 si un sir de text:

=A2 & ” ” & B1 & ” s-a finalizat”

Concateneaza un sir de text cu rezultatul functiei TEXT :

=”Data curenta:” & TEXT(A5, „dd-mmmm-yyyy”)

Metoda 2: folosind functia Excel CONCATENATE

Functia CONCATENATE in Excel este proiectata pentru a alipi diferite bucati de text sau a combina valori din mai multe celule intr-o singura celula.

Sintaxa Excel CONCATENATE este urmatoarea:

 =CONCATENATE (text1, [text2], …)

Mai jos veti gasi cateva exemple de utilizare a functiei CONCATENATE in Excel.

Observatie: In Excel 2019, Excel 2016, Excel Online si Excel Mobile, CONCATENATE este inlocuit cu functia CONCAT, care are exact aceeasi sintaxa. Desi functia CONCATENATE este pastrata pentru asigurarea compatibilitatii cu versiunile anterioare, se recomanda utilizarea noii functii CONCAT, deoarece Microsoft nu da promisiuni ca functia CONCATENATE va mai fi disponibil in versiunile viitoare de Excel.

Concatenarea valorilor mai multor celule

 Concateneaza valorile din A2 si A1:

= Concatenate(A2 , A1)

Concateneaza valorile din A2 si A1 separate cu un spatiu:

= Concatenate( A2 , ” ” ,  A1)

Concateneaza valorile din A2, B1 si un sir de text:

= Concatenate(A2 , ” ” , B1 , ” s-a finalizat”)

Concateneaza un sir de text cu rezultatul functiei TEXT :

= Concatenate(„Data curenta:” , TEXT(A5, „dd-mmmm-yyyy”))

Operatorul „&” sau functia Excel CONCATENATE? (ce sa alegem?):

Singura diferenta esentiala intre CONCATENATE si operatorul „&” este limita de 255 siruri a functiei Excel CONCATENATE (nu exista o astfel de limitare atunci cand utilizati ampersand-ul).

In afara de aceasta, nu exista nicio diferenta intre aceste doua metode de concatenare si nici nu exista vreo diferenta de viteza intre formulele CONCATENATE si „&”.

Personal folosesc operatorul pentru concatenare.

2. Siruri concatenate cu trecerea la randul urmator (line breaks)

Cel mai adesea veti dori sa uniti sirurile de text cu semnele de punctuatie si spatiile, asa cum am aratat in exemplul precedent. In unele cazuri poate fi necesar sa concatenati valorile celulelor cu trecerea pe o linie noua sau cu alte caractere speciale cum ar fi „, /, \ , etc.

In aceste cazuri trebuie sa stim codurile ASCII ale caracterelor speciale si sa folosim functia CHAR care transforma respectivul cod in caracterul special propriu-zis de care avem nevoie. Mai jos enumar codurile ASCII pentru cele mai importante caractere speciale de care veti avea nevoie la concatenare.

Pentru trecerea fortata la o linie noua codul ASCII este 10

Pentru trecerea fortata la inceputul liniei codul ASCII este 13

=A2 & CHAR(10) & CHAR(13) &

B2 & CHAR(10) & CHAR(13) &

C2 & CHAR(10) & CHAR(13) &

D2 & CHAR(10) & CHAR(13) & E2

Daca va intrebati cum am reusit sa scriu formula de mai sus pe mai multe randuri in bara de formule, retineti ca am apasat combinatia de taste ALT + Enter la finalul fiecarui rand (imediat dupa semnele &) 😊

Observatie: Pentru ca Excel sa afiseze caracterele rand nou (CHAR(10) si luare de la capat (CHAR(13), trebuie ca respectiva celula sa fie formatata ca Wrap Text, pentru aceasta:

• Selectati celula unde ati scris formula cu caracterele speciale

• Apasati combinatia de taste CTRL + 1 (pentru a intra in fereastra de optiuni Format Cells)

• Bifati butonul Wrap text din tab-ul Alignment (vedeti in poza de mai jos):

Ghilimelele au codul ASCII 34

Slash, adica / are codul ASCII 47

=”Ora este ” & CHAR(34) & A2 & ” AM” & CHAR(47) & „PM” & CHAR(34)

3. Concatenarea mai multor celule dintr-un range Excel :

Combinarea valorilor din mai multe celule ar putea necesita ceva efort, deoarece functia Excel CONCATENATE nu accepta pasarea directa a mai multor celule simultan, necesitand introducerea manuala a fiecarei adresa de celula in parte.

De pilda, pentru a concatena toate celulele de la A1 la A4, aveti nevoie de una dintre urmatoarele formule:

= CONCATENATE(A1, A2, A3, A4)

sau

= A1 & A2 & A3 & A4

Cand doriti concatenerarea unui numar relativ mic de celule, este in regula maniera de a lucra de mai sus.

Daca insa doriti sa concatenati un numar mare de celule devine obositor sa scrieti manual toate adresele de celule.

Va arat in continuare nu mai putin de 4 metode diferite de a concatena automat celule multiple

Metoda 3: Utilizati functia TRANSPOSE pentru a obtine enumerarea adreselor de celule pe care doriti sa le concatenati.

Cand trebuie sa concatenati un set mare de celule format din zeci sau sute de adrese puteti utiliza functia TRANSPOSE pentru a returna un tablou (vector) cu adresele respective.

In celula in care doriti sa concatenati, introduceti urmatoarea formula ce foloseste functia TRANSPOSE:

= TRANSPOSE (B1: B15)

In bara de formule, apasati F9 pentru a inlocui formula cu valori calculate. Ca rezultat, veti avea o serie de numere care trebuie concatenate.

={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}

Stergeti acoladele si adaugati CONCATENATE (inainte de prima valoare, apoi tastati paranteza de inchidere a functiei dupa ultima valoare si apasati Enter:

=Concatenate(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)

Exista un singur mare avantaj daca folositi metoda de concatenare cu functia Transpose: functioneaza pe orice versiune de Excel!

Si doua mari neajunsuri:

Problema cu aceasta metoda este ca rezultatul concatenarii NU este dinamic. Daca schimbati des valorile celulelor de concatenat, ar trebui sa reaplicati metoda de mai sus pentru a avea rezultatul updatat.

Un alt neajuns ar fi ca nu avem posibilitatea sa punem si un eventual separator intre valorile concatenate (de ex. semnul virgula)

Metoda 4: Utilizati instrumentul Flash Fill

Incepand cu versiunea de Excel 2013, Microsoft a implemnetat un instrument de introducere automata a datelor numit FLASH FILL

Sa presupunem ca avem tabelul de mai jos in care dorim sa completam automat coloana Nume Complet:

Scrieti in prima celula de completat (C2) numele complet asa cum doriti ca Excel sa l introduca automat in restul celulelor de pe coloana Nume Complet, apoi apasati butonul Flash Fill (sau apasati combinatia de taste CTRL + E) (vedeti animatia):

Ce este interesant la acest instrument este ca ne permite sa adaugam nu unul ba chiar mai multi separatori intre valorile celulelor de concatenat.

In exemplul de mai jos dorim sa concatenam pe coloana Nume Complet valorile de pe coloanele Nume, Initiala si Prenumele, ca in poza de mai jos:

Urmariti animatia ca sa vedeti cat de senzational este acest nou instrument numit Flash Fill:

Exista un mare avantaj daca folositi metoda de concatenare cu facilitatea Flash Fill: puteti crea aproape orice tip de macheta de concatenare (chiar si cu multipli separatori intre valorile concatenate)

Si doua mari dezavantaje:

La fel ca la metoda cu functia Transpose rezultatul concatenarii cu Flash Fill NU este dinamic. Nu este o metoda recomandata daca schimbati des valorile celulelor de concatenat.

Un alt neajuns ar fi ca acest instrument exista doar incepand cu versiunea Excel 2013 (care fie vorba intre noi este deja destul de veche 😊)

Metoda 5: Folosind functia CONCAT

Incepand cu versiunea Excel 2019 avem o noua functie ce permite concatenarea, functia CONCAT: ce isi propune sa o inlocuiasca pe mai vechea CONCATENATE.

Iata de ce:

Daca functiei Concatenate trebuia sa-i pasam manual fiecare celula de concatenat (=Concatenate(A, A, A3, …, A10)), noii functii Concat putem sa-i trimitem intreg blocul de celule (=Concat(A1:A10))

Frumos, nu? 😊

Nu chiar, in continuare avem de rezolvat o problema importanta: cum putem sa concatenam multe celule avand si posibilitatea de a adauga un separator intre ele.

O metoda ar fi cu functia Concat si cu o coloana ajutatoare.

Presupunem ca dorim sa concatenam toate Numele din tabelul de mai jos (cu virgula intre ele).

Pentru aceasta adaugam o coloana calculata ce contine deja semnul virgula dupa fiecare nume:

Apoi concatenam toate celulele de pe coloana calculata cu functia CONCAT:

=Concat(B2:B6)

Mai ramane doar sa scoatem ultima virgula:

=LEFT(D4,LEN(D4)-1)

Nu-mi plac solutiile cu coloane ajutatoare

Sa concluzionam cu functia Concat:

Avantajul mare este ca poate concatena direct toate celulele dintr-un range dat.

Dezavantajul sau major este ca nu stie nativ sa puna si un separator intre celulele concatenate (decat cu o coloana calculata)

Un alt dezavantaj il reprezinta faptul ca ea este disponibila doar incepand cu versiunea Excel 2019.

Metoda 6: Folosind functia TEXTJOIN (permite si adaugarea unui separator)

Functia TEXTJOIN din Excel concateneaza textul din mai multe intervale sau siruri (la fel ca si functia CONCAT), in plus insa, stie sa adauge si un separator intre valorile concatenate. Wow, maxim!

Puteti specifica un delimitator pentru a fi inclus intre valoarile celulelor de concatenat si ignora celulele goale!

Iata cum arata functia:

TEXTJOIN (delimitator, ignore_empty, text1, [text2], …)

Are 3 argumente obligatorii si 1 argument optional:

Delimitatorul va permite sa specificati orice caracter pentru separarea textului. Acesta poate fi o virgula, spatiu, ampersand sau oricare altul iti place.

Ignore_empty este un argument necesar. Daca este TRUE, celulele goale vor fi ignorate. Puteti include celule goale, setand aceasta valoare pe FALSE.

text1 va permite sa specificati o gama de valori ale celulelor pe care sa le concateneze, mai degraba decat sa le introduceti manual pe fiecare in parte.

[text2,…] este un argument optional care permite specificarea unor intervale suplimentare pentru a fi incluse daca intervalul dvs. nu este continuu.

Observati in tabelul de mai sus ca am lasat o celula necompletata tocmai pentru a vedea mai bine diferenta intre FALSE si TRUE la cel de-al doilea argument:

FALSE adica NU se ignora celulele goale

=TEXTJOIN(„,”,FALSE,A2:A6)

, returneaza: Ionescu,Popescu,,Vasilescu,Antonescu

TRUE adica se ignora celulele goale

=TEXTJOIN(„,”,TRUE,A2:A6)

, returneaza Ionescu,Popescu,Vasilescu,Antonescu

Metoda 7: folosind propria functie de concatenare facuta in VBA (UDF function)

Daca doriti sa aveti facilitatile functiei TextJoin de mai sus insa folositi o versiune mai veche de Excel (functia TextJoin functioneaza doar in versiunile Excel 2016 sau mai noi), atunci singura posibilitate ar fi sa va scrieti propria functie VBA care face concatenarea dorita.

Daca nu va pricepeti la programarea VBA, nu-i nimic, am scris eu aceasta functie pentru dvs.:

Codul functiei UDF (User Defined Function) care face concatenarea si cu separator:

Function ConcatenareCuSeparator(rng As Range, sep As String)
Dim oCelula As Range
Dim rez As String
For Each oCelula In rng.Cells
rez = rez & oCelula & sep
Next
rez = Left(rez, Len(rez) – 1)
ConcatenareCuSeparator = rez
End Function

Dupa cum vedeti, functiei i-am dat numele ConcatenareCuSeparator si are doua argumente (primul este setul de celule de concatenat, al doilea este separatorul)

Pentru a functiona codul de mai sus, trebuie sa-l copiati intr-un modul VBA, pentru aceasta apasati combinatia de taste ALT + F11 apoi faceti pasii din animatia de mai jos:

Pentru a testa functia ConcatenareCuSeparator, pur si simplu o folositi direct in orice celula Excel (vedeti poza de mai jos):

=ConcatenareCuSeparator(A2:A5, „,”)

Deocamdata atat am avut de spus cu privire la modalitatile de concatenare din Excel.

Voua care metoda v-a placut cel mai mult?

Nu ezitati sa-mi scrieti parerile voastre in zona de comentarii de mai jos.

Spor la invatat Excel!

Pe curand.