SUBSTITUTE nu înlocuiește textul (face diferența între majuscule și minuscule)
SUBSTITUTE e sensibil la majuscule: SUBSTITUTE(A2," SRL","") nu atinge „ srl” sau „ Srl”. Folosește SEARCH, care ignoră majusculele, ca să găsești poziția, și REPLACE ca să o elimini: =IFERROR(REPLACE(A2,SEARCH(" srl",A2),4,""),A2).
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Spre deosebire de majoritatea funcțiilor Excel (căutări, COUNTIF, comparații cu =), SUBSTITUTE compară textul exact, inclusiv majusculele. Pentru el, „SRL”, „srl” și „Srl” sunt trei texte diferite. Formula nu dă eroare; doar lasă neschimbate rândurile în care textul apare cu altă formă.
La date reale (nume de firme, orașe, produse introduse de oameni diferiți) majusculele variază aproape mereu, așa că o singură înlocuire acoperă doar o parte din rânduri.
Variante: SEARCH găsește poziția textului fără să țină cont de majuscule, iar REPLACE șterge exact acele caractere. Alternativ, poți imbrica mai multe SUBSTITUTE, câte unul pentru fiecare formă, sau poți uniformiza întâi textul cu UPPER/PROPER, dacă îți permiți să schimbi forma literelor.
De ce apare: cauzele frecvente
1Textul apare cu alte majuscule
=SUBSTITUTE(A2," SRL","") (A2 = „Alfa Construct srl”)=IFERROR(REPLACE(A2,SEARCH(" srl",A2),4,""),A2)SEARCH găsește „ srl” în orice formă, iar REPLACE elimină cele 4 caractere.
2Câteva forme cunoscute
=SUBSTITUTE(A2,"SRL","")=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," SRL","")," srl","")," Srl","")Fiecare SUBSTITUTE tratează o formă. Merge când formele sunt puține și știute.
3Uniformizare înainte de înlocuire
=SUBSTITUTE(A2,"bucuresti","București")=SUBSTITUTE(LOWER(A2),"bucuresti","București")LOWER aduce tot textul la litere mici, deci o singură formă de căutat. Restul textului devine și el cu litere mici.
Repară eroarea, direct în pagină
În B2:B4 trebuie să apară numele firmelor fără „SRL” la final. Formula =SUBSTITUTE(A2," SRL","") merge doar pentru Beta Logistic SRL; la celelalte, scrise „srl” și „Srl”, textul rămâne. Repară formulele din B2:B4 ca să elimine „SRL” indiferent de majuscule.
Indiciu
Există o funcție care găsește poziția unui text fără să țină cont de majuscule; o poți folosi împreună cu REPLACE.
Reparația
Găsește poziția cu SEARCH și elimin-o cu REPLACE, apoi copiază până la B4:
=IFERROR(REPLACE(A2,SEARCH(" srl",A2),4,""),A2)B2:B4 afișează Alfa Construct, Beta Logistic, Gama Design.
De ce merge
SUBSTITUTE caută exact „ SRL”, deci nu recunoaște „ srl” sau „ Srl”. SEARCH ignoră majusculele și întoarce poziția la care începe „ srl” în orice formă, iar REPLACE înlocuiește cele 4 caractere de acolo (spațiul și cele trei litere) cu un text gol. IFERROR lasă numele neschimbat dacă nu conține deloc SRL.
Alte forme ale mesajului
SUBSTITUTE case sensitiveSUBSTITUTE not workingSUBSTITUTE nu funcționeazăExcel replace case insensitive formula
Cum o eviți pe viitor
- Când înlocuiești text în date introduse de oameni, presupune că majusculele variază.
- Pentru eliminarea unui cuvânt indiferent de formă, folosește SEARCH + REPLACE.
- Pentru curățări mari, Find & Replace (Ctrl+H) ignoră implicit majusculele, dar modifică datele pe loc; fă-l pe o copie.
- Verifică rezultatul cu un filtru pe textul care ar fi trebuit să dispară.
Întrebări frecvente
De ce SUBSTITUTE face diferența, iar SEARCH nu?
Așa sunt definite: SUBSTITUTE și FIND sunt sensibile la majuscule, SEARCH, REPLACE, comparațiile și căutările nu. E bine să știi care din care face parte.
Care e diferența dintre SUBSTITUTE și REPLACE?
SUBSTITUTE înlocuiește un text cu alt text, oriunde apare. REPLACE înlocuiește caracterele de la o poziție dată, pe o lungime dată, fără să se uite ce conțin.
Erori înrudite
VLOOKUP și XLOOKUP nu fac diferența între majuscule și minuscule VLOOKUP, XLOOKUP, MATCH și COUNTIF tratează „ab12” și „AB12” ca pe aceeași valoare și întorc prima potrivire. Pentru o căutare care ține cont de majuscule folosește EXACT: =XLOOKUP(TRUE,EXACT(A2:A6,G1),C2:C6). #VALUE! în FIND și SEARCH (textul căutat nu există) FIND și SEARCH dau #VALUE! când nu găsesc textul căutat, de exemplu spațiul într-un nume format dintr-un singur cuvânt. Tratează cazul: =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2) sau adaugă caracterul căutat la final: =LEFT(A2,FIND(" ",A2&" ")-1). SUMIF returnează 0 la căutarea unei părți din text (lipsește wildcard-ul *) Fără caractere wildcard, criteriul "Caiet" se potrivește doar cu celulele care conțin exact „Caiet”, nu și cu „Caiet A4”. Pentru „începe cu” pune * la final, pentru „conține” pune * de ambele părți: =SUMIF(A2:A7,"Caiet*",B2:B7). Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
