Generische Formel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
Zusammenfassung
Eine Möglichkeit zum Bereinigen und Neuformatieren von Telefonnummern besteht darin, alle überflüssigen Zeichen zu entfernen und dann das in Excel integrierte Telefonnummernformat anzuwenden.
Diese obige Formel verwendet eine Reihe verschachtelter SUBSTITUTE-Funktionen, um Leerzeichen, Bindestriche, Punkte, Klammern und Kommas zu entfernen.
Sie müssen die tatsächlichen Ersetzungen an Ihre Daten anpassen.
Erläuterung
Die Formel läuft von innen nach außen, wobei jedes ERSATZ ein Zeichen entfernt.
Das innerste SUBSTITUT entfernt die linken Klammern, und das Ergebnis wird an das nächste SUBSTITUT übergeben, das die rechten Klammern entfernt, und so weiter.
Wenn Sie die SUBSTITUTE-Funktion verwenden, ist das Ergebnis Text. Da Sie kein Zahlenformat auf Text anwenden können, müssen wir den Text in eine Zahl konvertieren. Eine Möglichkeit, dies zu tun, besteht darin, Null (+0) hinzuzufügen, wodurch Zahlen im Textformat automatisch in Zahlen im numerischen Format konvertiert werden.
Schließlich wird das Telefonnummernformat "Spezial" angewendet (Spalte D).
Diese Seite erklärt benutzerdefinierte Zahlenformate und mit vielen Beispielen.
Leerraumtrick für bessere Lesbarkeit
Beim Verschachteln mehrerer Funktionen kann es schwierig sein, die Formel zu lesen und alle Klammern im Gleichgewicht zu halten. Excel kümmert sich nicht um zusätzliche Leerzeichen in einer Formel. Sie können also Zeilenumbrüche in die Formel einfügen, um die Formel besser lesbar zu machen. Zum Beispiel kann die obige Formel wie folgt geschrieben werden:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Beachten Sie, dass die Zelle in der Mitte mit Funktionsnamen oben und Ersetzungen unten angezeigt wird. Dies erleichtert nicht nur das Lesen der Formel, sondern auch das Hinzufügen und Entfernen von Substitutionen.
Mit demselben Trick können Sie auch verschachtelte IF-Anweisungen leichter lesen.
