Functii: LEFT, RIGHT, SEARCH, LEN, CONCATENATE

Cu acest articol mi-am propus sa incep o noua serie in care sa vorbesc despre formulele pe care le putem folosi in Excel, in primul vorbesc despre LEFT, RIGHT, SEARCH, LEN si CONCATENATE.

Aici am totusi o problema, oricat as fi eu de sec nu pot sa incep sa scriu articole in care prezint doar o formula pentru ca ar fi foarte reci si PLICTISITOARE. Voi incerca in schimb sa pun fiecare formula in contextul in care am putea sa o folosim.

Bun, atunci hai sa vorbim despre Gina, ea se ocupa de baza de date cu angajatii din companie. Conducerea companiei a decis sa ia o masura care sa salveze compania de la faliment: (tineti-va bine) de acum incolo vor scrie numele angajatilor pe sistemul PRENUME NUME in baza lor de date.

Tabel Nume

Tinand cont ca firma are 17452 de angajati eu zic ca ar trebui sa ne scoatem din cap ideea de a face modificarea manual. In acest articol veti vedea un mod foarte rapid si usor prin care putem inversa numele si prenumele.

Cum procedam

Ca sa o salvam pe Gina de cateva zile de “distractie la maxim”, vom extrage pe rand, prima data numele de familie si apoi prenumele si le vom lipi la loc in cealalta ordine. Evident ca nu manual ci folosind formule.

LEFT, RIGHT

Vrem ca din coloana de nume intreg sa extragem atat numele cat si prenumele. Putem face asta folosind functiile LEFT si RIGHT care au urmatoarea sintaxa:

LEFT (text, numar caractere)

RIGHT (text, numar caractere)

Folosind aceste 2 formule foarte usor sa extragem numele si prenumele din Popescu Ion:

Nume = LEFT (Celula in care se afla Popescu Ion, 8)

Prenume = RIGHT (celula in care se afla Popescu Ion, 3)

* vorbesc de celule in Excel si nu de alt fel de celule 🙂

Avem totusi o problema aici, nu pe toti angajatii ii cheama la fel, nu toti angajatii au numele si prenumele de aceeasi lungime a caracterelor. Era bine daca era totusi asta un criteriu de angajare (glumesc).

Asta inseamna ca nu ne putem baza doar pe LEFT si RIGHT, mai avem nevoie de inca o formula care sa detecteze pe ce pozitie se afla spatiul liber intre nume si prenume, hmm, oare exista asa ceva…

SEARCH

Raspunsul scurt e da, functia SEARCH ne poate ajuta sa detectam unde se afla spatiul liber, mai jos vedeti sintaxa si modul in care detectam acel spatiu liber:

SEARCH (textul pe care il cautam, celula in care se afla Popescu Ion)

SEARCH (” “, celula in care se afla Popescu Ion)

SEARCH va returna o valoare numerica, in cazul nostru de mai sus, 8.

Extragem numele

Avem toate armele la dispozitie, haideti sa extragem numele:

NUME =LEFT(Popescu Ion,SEARCH(” “,Popescu Ion))

Functii Excel Text

Extragem prenumele

Mai avem o problema, SEARCH ne spune doar pe ce pozitie se afla spatiul, noi trebuie sa aflam cate caractere are prenumele. Nashpa, aiurea, cam nasol, eu credeam ca i-am dat de cap. Ne gandim logic, numarul de caractere pentru prenume este egal cu lungimea numelui intreg (LEN) – pozitia unde se afla spatiul. Adica ceva de genul:

# caractere prenume = LEN (Popescu Ion) – SEARCH(” “,Popescu Ion)

Asta inseamna ca vom extrage prenumele in felul urmator:

PRENUME = RIGHT(Popescu Ion,LEN(Popescu Ion)-SEARCH(” “,Popescu Ion))

Functii Excel Text

Si acum le lipim la loc (CONCATENATE)

Am terminat ce era mai dificil tot ce trebuie sa facem este sa le lipim la loc in ordine inversa. Pentru asta folosim CONCATENATE.

CONCATENATE(Text1, Text2, …)

Nume intreg revizuit = CONCATENATE(Prenume,” “, Nume)

“_” e pentru ca vreau sa adaug si un spatiu intre nume si prenume.

Functii Excel Text

Acum la final

Cand am inceput sa scriu acest articol credeam ca o sa vorbesc despre LEFT si RIGHT, in timp ce scriam au mai aparut si alte formule. Cu un click aici poti sa descarci fisierul Excel pe care am lucrat eu. Mai jos o scurta sumarizare a formulelor pe care le-am folosit:

  • LEFT: extragem un numar de caractere dintr-un text, numerotarea incepe din partea stanga
  • RIGHT: extragem un numar de caractere dintr-un text, numerotarea incepe din partea dreapta
  • SEARCH: cauta unul sau mai multe caractere dintr-un text si returneaza pozitia pe care se afla, numerotarea incepe din partea stanga
  • LEN: iti returneaza numarul de caractere dintr-un text

S-ar putea sa fie si modalitati mai usoare de a rezolva situatia, chiar te rog sa folosesti sectiunea de comentarii. Daca vrei sa fii la curent putem tine legatura pe Facebook, Google+, Email.

Vrei sa afli mai multe despre functii in Excel?

Am un eBook pentru tine, afla mai multe aici.


  • dragos

    As vrea sa concatenez (inlantui) mai multe numerele de telefon din 157 (de exemplu) celule aflate una sub alta intr-una singura (celula) si cu virgula intre ele (numerele de telefon). Nu doresc sa lucrez manual gen CONCATENATE(Text1, Text2, …), ci printr-o singura formula. Ma poate ajuta cineva?

    • Pot sa incerc eu :). Scrii formula in felul urmator concatenate(transpose(A1:A157 & “, “)). Nu dai enter inca. Selectezi transpose(A1:A157 & “, “), apesi F9 si apoi scoti din formula generata {}. Ar trebui sa functioneze.

      • dragos

        Multumesc ffffffff mult.A functionat.Dragos
        From: Disqus
        To: chiriac7@yahoo.com
        Sent: Thursday, February 19, 2015 7:52 AM
        Subject: Re: Comment on Functii: LEFT, RIGHT, SEARCH, LEN, CONCATENATE
        #yiv6887679840 #yiv6887679840 a:hover, #yiv6887679840 a:hover span {color:#1188d2!important;}#yiv6887679840 .yiv6887679840button-cta:hover {color:#ffffff!important;background-color:#1188d2!important;}#yiv6887679840 .yiv6887679840button-cta:hover span {color:#ffffff!important;}#yiv6887679840 #yiv6887679840 #yiv6887679840 #yiv6887679840outlook a {padding:0;}#yiv6887679840 body {width:100% !important;}#yiv6887679840 .yiv6887679840ReadMsgBody {width:100%;}#yiv6887679840 .yiv6887679840ExternalClass {width:100%;display:block;}#yiv6887679840 _filtered #yiv6887679840 {}#yiv6887679840 .yiv6887679840content {width:100%;}#yiv6887679840 table {border-collapse:collapse;}#yiv6887679840 h2.yiv6887679840headline {font-weight:700;font-size:20px!important;margin-bottom:5px;}#yiv6887679840 .yiv6887679840button-cta {display:block!important;padding:0!important;}#yiv6887679840 div.yiv6887679840header {padding-top:20px;}#yiv6887679840 div.yiv6887679840footer {padding-bottom:20px;}#yiv6887679840 #yiv6887679840 #yiv6887679840 p.yiv6887679840mod-tools a:hover {color:white!important;background:#8c989f!important;}#yiv6887679840 _filtered #yiv6887679840 {}#yiv6887679840 td.yiv6887679840avatar img, #yiv6887679840 td.yiv6887679840spacer img {width:28px!important;}#yiv6887679840 “Pot sa incerc eu :). Scrii formula in felul urmator concatenate(transpose(A1:A157 & “, “)). Nu dai enter inca. Selectezi transpose(A1:A157 & “, “), apesi F9 si apoi scoti din formula generata {}. Ar trebui sa functioneze.” | |
        | |  Settings | |
        |   |

        | |

        | |
        |
        A new comment was posted on excelninja.ro
        |
        | |
        |
        | |
        Radu Popa
        Pot sa incerc eu :). Scrii formula in felul urmator concatenate(transpose(A1:A157 & “, “)). Nu dai enter inca. Selectezi transpose(A1:A157 & “, “), apesi F9 si apoi scoti din formula generata {}. Ar trebui sa functioneze. 12:52 a.m., Thursday Feb. 19 | Other comments by Radu Popa |   |
        |
        |   | Reply to Radu Popa |   |

        |
        |   |

        |

        | Radu Popa’s comment is in reply to dragos: |
        |   |
        | | As vrea sa concatenez (inlantui) mai multe numerele de telefon din 157 (de exemplu) celule aflate una sub alta intr-una singura (celula) si cu virgula …Read more |
        |
        | |

        | |

        | |
        | You’re receiving this message because you’re signed up to receive notifications about replies to disqus_SjorkWvO8B. You can unsubscribe from emails about replies to disqus_SjorkWvO8B by replying to this email with “unsubscribe” or reduce the rate with which these emails are sent by adjusting your notification settings. | | |

        | |

      • dragos

        Multam fain. A mers. Dragos

  • albsz

    Buna,

    dar cum se poate sa mut (nu sa copiez), sa separ cele doua cuvinte?

    pornim de la “Popescu Ion” intr-o celula

    aplicam sa zicem una din functii:left sau right si

    intr-o celula sa ramana “Popescu” si in alta “Ion”

    • Cam asta am facut in tutorial, ce ar ramane de facut ar fi ca sa elimini formulele de pe celulele create, folosind paste special

  • albsz

    pentru cunascatori, floare la ureche, pentru cei ca mine mai greu de inteles

  • cristina

    buna,
    daca pornim de la “Popescu Ion Vasile” cum il scot doar pe Vasile ? 🙂

    multumesc

  • Pingback: Separa numele de prenume in Excel | excelninja.ro()