Excel

Fjern numeriske tegn fra celle

Strip Numeric Characters From Cell

Excel-formel: Fjern numeriske tegn fra celleGenerisk formel
{= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (A1, ROW ( INDIRECT ('1:100')),1)+0), MID (A1, ROW ( INDIRECT ('1:100')),1),''))}
Resumé

For at fjerne numeriske tegn fra en tekststreng kan du bruge en formel baseret på TEXTJOIN-funktion . I det viste eksempel er formlen i C5:

 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365 .



Forklaring

Excel har ikke en måde at kaste bogstaverne i en tekststreng til en array direkte i en formel. Som en løsning bruger denne formel MID-funktionen med hjælp fra ROW- og INDIRECT-funktionerne for at opnå det samme resultat. Formlen i C5, kopieret ned, er:



 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

Dette ser ret kompliceret ud, men kernen er, at vi opretter en array af alle tegn i B5, og test hvert tegn for at se, om det er et tal. I så fald kasserer vi værdien og erstatter den med en tom streng (''). Hvis ikke, tilføjer vi det ikke-numeriske tegn til et 'behandlet' array. Endelig bruger vi TEXTJOIN-funktionen (ny i Excel 2019) til at sammenkæde alle tegn sammen og ignorere tomme værdier.

excel-indeks og matche flere kriterier

Arbejder indefra og ud, den MID-funktion bruges til at udtrække teksten i B5, et tegn ad gangen.Nøglen er RÆKKE og INDIREKTE uddrag her:



 
 ROW ( INDIRECT ('1:100'))

der spinder et array op, der indeholder 100 numre som dette:

{1,2,3,4,5,6,7,8 .... 99,100}

Bemærk: 100 repræsenterer det maksimale antal tegn, der skal behandles. Skift, så det passer til dine data, eller brug LEN-funktionen som forklaret nedenfor.



Denne matrix går ind i MID-funktionen som start_num argument. Til num_chars , vi bruger 1.

hvordan man ændrer x-akseetiketter i Excel

MID-funktionen returnerer en matrix som denne:

 
{'3''4''6''5''3'' ''J''i''m'' ''M''c''D''o''n''a''l''d'''''''...}

Bemærk: ekstra elementer i arrayet fjernet for læsbarhed.

Til denne matrix tilføjer vi nul. Dette er et simpelt trick, der tvinger Excel til at tvinge tekst til et tal. Numeriske tekstværdier som '1', '2', '3', '4' osv. Konverteres uden fejl, men ikke-numeriske værdier mislykkes og kaster en #VALUE-fejl. Vi bruger HVIS-funktion med ISERR-funktion for at fange disse fejl. Når vi ser en fejl, ved vi, at vi har et ikke-numerisk tegn, så vi bringer det tegn ind i det behandlede array med en anden MID-funktion:

 
 MID (B5, ROW ( INDIRECT ('1:100')),1)

Hvis gør det ikke få en fejl, vi ved, at vi har et tal, så vi indsætter en tom streng ('') i arrayet i stedet for nummeret.

Det endelige array-resultat går ind i TEXTJOIN-funktionen som tekst1 argument. Til afgrænsning , bruger vi en tom streng ('') og for ignorere vi leverer SAND. TEXTJOIN derefter sammenkædes alle ikke-tomme værdier i arrayet og returnerer resultatet.

Præcis array længde

I stedet for at hardcode et tal som 100 i INDIRECT, kan du bruge LEN-funktion at oprette en matrix med det faktiske antal tegn i cellen sådan:

 
 MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1)

LEN returnerer antallet af tegn i cellen som et tal, der bruges i stedet for 100. Dette gør det muligt for formlen at skalere op til et hvilket som helst antal tegn automatisk.

Fjernelse af ekstra plads

Når du stripper numeriske tegn, kan du have ekstra mellemrumstegn tilovers. For at fjerne forreste og bageste mellemrum og normalisere mellemrum mellem ord kan du pakke formlen vist på denne side inde i TRIM-funktion :

hvordan opretter du et cirkeldiagram i Excel
 
= TRIM (formula)

Med SEKVENS

I Excel 365 , den nye SEKVENS-funktion kan erstatte ROW + INDIRECT-koden ovenfor:

 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, SEQUENCE ( LEN (B5)),1)+0), MID (B5, SEQUENCE ( LEN (B5)),1),''))

Her bruger vi SEQUENCE + LEN til at opbygge en matrix med den rigtige længde i et trin.

Med LET

Vi kan yderligere strømline denne formel med LET-funktion . Fordi arrayet oprettes to gange ovenfor med SEQUENCE og LEN, kan vi definere array som en variabel og oprette det kun en gang:

 
= LET (array, SEQUENCE ( LEN (B5)), TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5,array,1)+0), MID (B5,array,1),'')))

Her er værdien af array indstilles kun en gang og bruges derefter to gange i MID-funktionen.

Forfatter Dave Bruns


^