#Excel – Eliminare diacritice din nume – Funcția SUBSTITUTE()


 

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

Reclame

3 comentarii

  1. 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

    Apreciat de 2 persoane

Comentariile sunt închise.