Formula generică
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
rezumat
Pentru a elimina caractere numerice dintr-un șir de text, puteți utiliza o formulă bazată pe funcția TEXTJOIN. În exemplul prezentat, formula din C5 este:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Explicaţie
Excel nu are o modalitate de a arunca literele într-un șir de text într-o matrice direct într-o formulă. Ca soluție, această formulă utilizează funcția MID, cu ajutorul funcțiilor ROW și INDIRECT pentru a obține același rezultat. Formula din C5, copiată, este:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Acest lucru pare destul de complicat, dar esența este că creăm o serie de toate caracterele din B5 și testăm fiecare caracter pentru a vedea dacă este un număr. Dacă da, eliminăm valoarea și o înlocuim cu un șir gol (""). Dacă nu, adăugăm caracterul nenumeric la o matrice „procesată”. În cele din urmă, folosim funcția TEXTJOIN (nouă în Excel 2019) pentru a concatena toate caracterele împreună, ignorând valorile goale.
Funcționând din interior spre exterior, funcția MID este utilizată pentru a extrage textul din B5, câte un caracter odată. Cheia este fragmentul ROW și INDIRECT aici:
ROW(INDIRECT("1:100"))
care învârte o matrice care conține 100 de numere ca aceasta:
(1,2,3,4,5,6,7,8… .99.100)
Notă: 100 reprezintă maximum de caractere de procesat. Modificați pentru a se potrivi cu datele dvs. sau utilizați funcția LEN așa cum este explicat mai jos.
Această matrice intră în funcția MID ca argument start_num . Pentru num_chars , folosim 1.
Funcția MID returnează o matrice ca aceasta:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Notă: elementele suplimentare din matrice au fost eliminate pentru lizibilitate.
La această matrice, adăugăm zero. Acesta este un truc simplu care forțează Excel să constrângă textul către un număr. Valorile textului numeric precum „1”, „2”, „3”, „4” etc. sunt convertite fără erori, dar valorile nenumerice vor eșua și vor genera o eroare #VALUE. Folosim funcția IF cu funcția ISERR pentru a detecta aceste erori. Când vedem o eroare, știm că avem un caracter nenumeric, așa că aducem acel caracter în matricea procesată cu o altă funcție MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Dacă nu primim o eroare, știm că avem un număr, așa că introducem un șir gol ("") în matrice în locul numărului.
Rezultatul matricei finale intră în funcția TEXTJOIN ca argument text1. Pentru delimitator, folosim un șir gol (""), iar pentru ignore_empty furnizăm TRUE. TEXTJOIN apoi concatenează toate valorile ne-goale din matrice și returnează rezultatul.
Lungimea matricei precise
În loc să codificați un număr ca 100 în INDIRECT, puteți utiliza funcția LEN pentru a construi o matrice cu numărul real de caractere din celulă astfel:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN returnează numărul de caractere din celulă ca număr, care este utilizat în loc de 100. Aceasta permite formulei să se scaleze automat până la orice număr de caractere.
Eliminarea spațiului suplimentar
Când eliminați caractere numerice, este posibil să rămâneți spațiu suplimentar. Pentru a elimina spațiile de început și de final și pentru a normaliza spațiile dintre cuvinte, puteți înfășura formula afișată pe această pagină în funcția TRIM:
=TRIM(formula)
Cu SECVENȚĂ
În Excel 365, noua funcție SEQUENCE poate înlocui codul ROW + INDIRECT de mai sus:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Aici, folosim SEQUENCE + LEN pentru a construi o matrice cu lungimea corectă într-un singur pas.
Cu LET
Putem simplifica în continuare această formulă cu funcția LET. Deoarece matricea este creată de două ori mai sus cu SEQUENCE și LEN, putem defini matricea ca o variabilă și o putem crea o singură dată:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Aici valoarea matricei este setată o singură dată, apoi utilizată de două ori în interiorul funcției MID.
