Funcția SUBSTITUTE() poate foarte utilă pentru corectarea textului în operațiunile de import sau după caz.
În multe cazuri am văzut oameni (inclusiv pe mine) care fac operațiunile de înlocuire a caracterelor cu ajutorul operațiunii Office Find and Replace (Ctrl+H).
Conform articolului citat mai sus Substitute() se folosește pentru a înlocui toate caracterele de un anumit fel cu alte caractere sau chiar a le elimina.
În exemplul de mai jos avem un caz concret. Caracterele românești sunt din ce în ce mai des folosite (inclusiv în documentele oficiale :) ) dar există suficient de multe probleme legate de sistemele de e-mail de exemplu sau de autentificare. Poate greșesc și forțez puțin cazul de utilizare. Pentru corecturi/completări vă rog folosiți cu încredere secțiunea comentarii.
În exemplul nostru ne este solicitată crearea automată a unui nume de utilizator de maxim 20 de caractere care să fie de forma prenume.nume.
Pentru acest lucru avem nevoie de mai multe funcții SUBSTITUTE() imbricate.
Alternativele ca formule la substitute() sunt combinațiile de funcții FIND() sau SEARCH() și REPLACE(). Nu uitați că funcția FIND() este case sensitive. De asemenea, returnează eroare dacă nu găsește caracterul în textul de căutare.
Problema cu funcția Replace() este că înlocuiește doar primul caracter întâlnit. Pe când Substitute() le înlocuiește pe toate.
Iată un exemplu în care căutăm un caracter cu Find() și îl înlocuim cu replace() și unul cu o formula substitute()
În exemplul nostru de început, pentru a înlocui toate diacriticele, spațiul sau liniuța am imbricat mai multe funcții formula din C3 fiind:
=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(B3));”-„;””);”ă”;”a”);”î”;”i”);”ș”;”s”);”ț”;”t”);”â”;”a”);” „;””)&”.”&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A3));”-„;””);”ă”;”a”);”î”;”i”);”ș”;”s”);”ț”;”t”);”â”;”a”);” „;””);20)
Puteți descărca fișierul Excel cu acest exemplu de la adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq_pgh5GWrX7vs2A2QQ
Sper să fie util cuiva! Nu uitați de comentarii pentru întrebări.
Recomandare de citit: Cele mai eficiente zece metode de curățare a datelor
Valy Greavu - Live Blog 
Salutare,
Daca ajuta pe cineva, am scris o functie pentru inlocuirea diacriticelor din limba romana.
E o varianta simplificata pentru a nu imbrica o sumedenie de functii SUBSTITUTE.
Codul de mai jos se introduce intr-un modul:
Public Function ReplaceROChars(str As String) As String
Dim dCharArr As Variant
Dim sCharArr As Variant
Dim i As Byte
Dim jChr As Integer
dCharArr = Array(258, 259, 194, 226, 206, 238, 218, 219, 350, 351, 538, 539, 354, 355, 536, 537)
sCharArr = Array(65, 97, 65, 97, 73, 105, 83, 115, 83, 115, 84, 116, 84, 116, 83, 115)
For i = LBound(dCharArr) To UBound(dCharArr)
For j = 1 To Len(str)
jChr = AscW(Mid(str, j, 1))
‘––
If jChr = dCharArr(i) Then
Mid(str, j) = Chr(sCharArr(i))
End If
Next j
Next i
‘––
ReplaceROChars = str
End Function
Apoi pentru a folosi functia se introduce in orice celula:
=ReplaceROChars(A1)
unde A1 este referinta celulei care contine textul cu diacritice.
Cheers,
Adrian Botoc
ApreciazăApreciat de 2 persoane
Multumim mult Adrian pentru partajare.
ApreciazăApreciază
Multumim de explicatii.
Aveti pe mail si o varianta folosind Power Query
ApreciazăApreciază