Excel

Entferne numerische Zeichen aus der Zelle

Strip Numeric Characters From Cell

Excel-Formel: Entferne numerische Zeichen aus der ZelleAllgemeine Formel |_+_| Zusammenfassung

Um numerische Zeichen aus einer Textzeichenfolge zu entfernen, können Sie eine Formel verwenden, die auf dem TEXTJOIN-Funktion . Im gezeigten Beispiel lautet die Formel in C5:



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

Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365 .

Erläuterung

Excel hat keine Möglichkeit, die Buchstaben in einer Textzeichenfolge in ein umzuwandeln Array direkt in einer Formel. Als Workaround verwendet diese Formel die MID-Funktion mit Hilfe der ROW- und INDIRECT-Funktionen, um das gleiche Ergebnis zu erzielen. Die nach unten kopierte Formel in C5 lautet:





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

Das sieht ziemlich kompliziert aus, aber das Wesentliche ist, dass wir eine Array aller Zeichen in B5 und testen Sie jedes Zeichen, um zu sehen, ob es eine Zahl ist. Wenn ja, verwerfen wir den Wert und ersetzen ihn durch ein leerer String (''). Wenn nicht, fügen wir das nicht numerische Zeichen zu einem 'verarbeiteten' Array hinzu. Schließlich verwenden wir die Funktion TEXTJOIN (neu in Excel 2019), um alle Zeichen miteinander zu verketten und leere Werte zu ignorieren.

wie man eine if then Aussage in Excel macht

Von innen nach außen arbeiten, die MID-Funktion wird verwendet, um den Text in B5 zeichenweise zu extrahieren.Der Schlüssel ist der REIHE und INDIREKT Ausschnitt hier:



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

die ein Array mit 100 Zahlen wie folgt aufdreht:

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

Hinweis: 100 steht für die maximal zu verarbeitende Zeichenzahl. Passen Sie Ihre Daten entsprechend an oder verwenden Sie die LEN-Funktion wie unten beschrieben.

Dieses Array geht in die MID-Funktion als start_num Streit. Zum num_chars , wir verwenden 1.

Wo ist das Namensfeld in Excel 2013?

Die MID-Funktion gibt ein Array wie folgt zurück:

 
 ROW ( INDIRECT ('1:100'))

Hinweis: Zusätzliche Elemente im Array wurden aus Gründen der Lesbarkeit entfernt.

Zu diesem Array fügen wir Null hinzu. Dies ist ein einfacher Trick, der Excel zwingt, Text in eine Zahl zu zwingen. Numerische Textwerte wie '1','2','3','4' usw. werden ohne Fehler konvertiert, aber nicht numerische Werte schlagen fehl und geben einen #VALUE-Fehler aus. Wir nehmen das WENN-Funktion mit dem ISERR-Funktion um diese Fehler abzufangen. Wenn wir einen Fehler sehen, wissen wir, dass wir ein nicht numerisches Zeichen haben, also nehmen wir dieses Zeichen in die Verarbeitung auf Array mit einer anderen MID-Funktion:

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

Wenn nicht einen Fehler erhalten, wissen wir, dass wir eine Zahl haben, also fügen wir anstelle der Zahl eine leere Zeichenfolge ('') in das Array ein.

Das endgültige Array-Ergebnis geht in die TEXTJOIN-Funktion als Text 1 Streit. Zum Trennzeichen , verwenden wir eine leere Zeichenfolge ('') und für ignore_leer wir liefern WAHR. TEXTJOIN dann verkettet alle nicht leeren Werte im Array und gibt das Ergebnis zurück.

Präzise Array-Länge

Anstatt eine Zahl wie 100 in INDIREKT fest zu codieren, können Sie die LEN-Funktion um ein Array mit der tatsächlichen Anzahl von Zeichen in der Zelle wie folgt zu erstellen:

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

LEN gibt die Anzahl der Zeichen in der Zelle als Zahl zurück, die anstelle von 100 verwendet wird. Dadurch kann die Formel automatisch auf eine beliebige Anzahl von Zeichen skaliert werden.

Entfernen von zusätzlichem Speicherplatz

Wenn Sie numerische Zeichen entfernen, bleiben möglicherweise zusätzliche Leerzeichen übrig. Um führende und nachgestellte Leerzeichen zu entfernen und Leerzeichen zwischen Wörtern zu normalisieren, können Sie die auf dieser Seite gezeigte Formel in das TRIM-Funktion :

wie man Prozent in Excel berechnet
 
 MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1)

Mit SEQUENZ

In Excel 365 , das neue SEQUENCE-Funktion kann den obigen ROW + INDIRECT-Code ersetzen:

 
= TRIM (formula)

Hier verwenden wir SEQUENCE + LEN, um in einem Schritt ein Array mit der richtigen Länge zu erstellen.

Mit LET

Wir können diese Formel mit dem LET-Funktion . Da das Array oben zweimal mit SEQUENCE und LEN erstellt wird, können wir Array als Variable definieren und es nur einmal erstellen:

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

Hier Wert von Array wird nur einmal eingestellt und dann zweimal innerhalb der MID-Funktion verwendet.

Autor Dave Bruns


^