Wie kann ich aus einer Excel Zelle bestimmte Zeichen löschen?
Ich möchte aus einer Liste von verschieden geschriebenen Telefonnummern, alle Zeichen ausser den Zahlen, Leerzeichen, + , und () löschen?
Also die Daten liegen z.B. so vor: (0)12345435 +49 2341432 43242 +49 4324-4324 Tel.: 3424234324 Telefon: 434321 / 4234
Ich will immer nur die oben genannten Zeichen erhalten, alles andere entfernen.
9 Antworten
Da hat mir GF wieder mal eine Riesen-Antwort gelöscht! Schschsch!!!
Deshalb leider nur nochmal kurz die (lange) Formel, wenn Makros vermieden werden sollen (DH an Subopt!, insbes. für die Idee mit C_erlaubt!):
Formel für 32 Zeichen (xl hat leider keine verketten-Fkt für mehr als 2 Glieder analog SUMME(), so dass auch keine Matrixformeln greifen -dabei wäre das so einfach zu programmieren!), also muss man jedes Zeichen einzeln samt Fehlerroutine behandeln, was zu folgendem Formelungetüm führt:
=WENN(WENNFEHLER(CODE(TEIL(A1;1;1));58)>57;"";TEIL(A1;1;1))&WENN(WENNFEHLER(CODE(TEIL(A1;2;1));58)>57;"";TEIL(A1;2;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;3;1));58)>57;"";TEIL(A1;3;1))&WENN(WENNFEHLER(CODE(TEIL(A1;4;1));58)>57;"";TEIL(A1;4;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;5;1));58)>57;"";TEIL(A1;5;1))&WENN(WENNFEHLER(CODE(TEIL(A1;6;1));58)>57;"";TEIL(A1;6;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;7;1));58)>57;"";TEIL(A1;7;1))&WENN(WENNFEHLER(CODE(TEIL(A1;8;1));58)>57;"";TEIL(A1;8;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;9;1));58)>57;"";TEIL(A1;9;1))&WENN(WENNFEHLER(CODE(TEIL(A1;10;1));58)>57;"";TEIL(A1;10;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;11;1));58)>57;"";TEIL(A1;11;1))&WENN(WENNFEHLER(CODE(TEIL(A1;12;1));58)>57;"";TEIL(A1;12;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;13;1));58)>57;"";TEIL(A1;13;1))&WENN(WENNFEHLER(CODE(TEIL(A1;14;1));58)>57;"";TEIL(A1;14;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;15;1));58)>57;"";TEIL(A1;15;1))&WENN(WENNFEHLER(CODE(TEIL(A1;16;1));58)>57;"";TEIL(A1;16;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;17;1));58)>57;"";TEIL(A1;17;1))&WENN(WENNFEHLER(CODE(TEIL(A1;18;1));58)>57;"";TEIL(A1;18;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;19;1));58)>57;"";TEIL(A1;19;1))&WENN(WENNFEHLER(CODE(TEIL(A1;20;1));58)>57;"";TEIL(A1;20;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;21;1));58)>57;"";TEIL(A1;21;1))&WENN(WENNFEHLER(CODE(TEIL(A1;22;1));58)>57;"";TEIL(A1;22;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;23;1));58)>57;"";TEIL(A1;23;1))&WENN(WENNFEHLER(CODE(TEIL(A1;24;1));58)>57;"";TEIL(A1;24;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;25;1));58)>57;"";TEIL(A1;25;1))&WENN(WENNFEHLER(CODE(TEIL(A1;26;1));58)>57;"";TEIL(A1;26;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;27;1));58)>57;"";TEIL(A1;27;1))&WENN(WENNFEHLER(CODE(TEIL(A1;28;1));58)>57;"";TEIL(A1;28;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;29;1));58)>57;"";TEIL(A1;29;1))&WENN(WENNFEHLER(CODE(TEIL(A1;30;1));58)>57;"";TEIL(A1;30;1))
&WENN(WENNFEHLER(CODE(TEIL(A1;31;1));58)>57;"";TEIL(A1;31;1))&WENN(WENNFEHLER(CODE(TEIL(A1;32;1));58)>57;"";TEIL(A1;32;1))
kannst Du hier kopieren und in Deinem Blatt einfügen.
Für xl ist diese Formellänge übrigens kein Problem, auch nicht in vielfacher Ausführung!
kannst Du hier kopieren und in Deinem Blatt einfügen.
einfügen in die Bearbeitungszeile bzw offene Zelle, nicht "drauf"-kopieren, sonst bewirken die Umbrüche die Verteilung auf viele Zellen untereinander!
Versuch es mit "Suchen und Ersetzen".
Tastenbefehl: CTRL + F
Das Problem ist ja, das ich nicht immer nur Zeichen am Anfang oder am Ende entfernen will, und ich will auch nicht suchen und ersetzen für jedes Zeichen der ASCII Tabelle machen. Ich suche eine Möglichkeit einfach komplett alle Zeichen zu entfernen, außer die die ich angebe.
Entweder Du gehst oben in die Bearbeitungszeile oder Du gehst mit einem Doppelklick direkt in die Zelle, dann kannst du mit den Richtungstasten navigieren und überflüssige Zeichen einzeln entfernen.
Viel Erfolg :)
Du könntest dir die Daten in ein LibreOffice Calc Dokument kopieren, und dort mit den Regulären Ausdrücken ,die Zeichen alle auf einmal angeben, und dann mit Suchen und Ersetzen alle Zeichen auf einmal entfernen. (LO Calc unterstützt regex auch beim Suchen und Ersetzen)
Dann kopierst du dir den Spaß wieder zurück nach Excel und fertig ;)
Aber ich denke, die Makrolösung von Suboptimierer ist deutlich angenehmer und Zeitsparender, als dass hin und her kopieren.
Wenn du Ihn lieb fragst, erläutert er dir sicher auch das Makro noch.
Liebe Grüße,
iMPerFekTioN
Ich würde das über ein Makro lösen.
Sub FmtTel(rTels As Range) Const C_ERLAUBT = "0123456789+()" '/-" Dim i As Integer Dim rTel As Range Dim sTel
For Each rTel In rTels rTel.NumberFormat = "@" sTel = "" For i = 1 To Len(rTel.Value) If InStr(1, C_ERLAUBT, Mid(rTel.Value, i, 1)) > 0 Then _ sTel = sTel & Mid(rTel.Value, i, 1) Next rTel.Value = sTel Next End Sub
Dann würde ich mit select case Ord(Mid(...)) arbeiten, weil dann kannst du die Ziffern mit Case 48 To 57, 40, 41, 43 abgelten.
noch was: das Füllen von C_Erlaubt bricht vermutlich nach dem mittleren " ab und es wird nur zufällig kein Fehler gemeldet, weil danach Leer und '/-" als so interpretierter Kommentar folgt.
Vermutlich muss das mittlere " drei- oder 4-fach sein: """, habs aber nicht ausprobiert
Das verstehe ich nicht. Das '/-" ist ein mit Absicht entwerteter Code. Ich habe die Zeichen in einem Test zugelassen.
danke für den Hinweis, Ord() kannte ich gar nicht, hab immer mit Chr(zB10) gearbeitet, da ist Code ja nicht mehr nötig, oder mit Asc("Zeichen").
Man lernt wirklich nie aus!
Aber hier ist Deine Idee ohnehin besser!
komisch habs bei mir (xl2010) grad ausprobiert: '/-" wird als Kommentar markiert, aber es reichen 2 "": Nur so gehts bei mir richtig:
Sub FmtTel_()
Const C_ERLAUBT = "0123456789+()"" '/-"
Debug.Print C_ERLAUBT
End Sub
Deine Fassung schließt das Leerzeichen und die Zeichen "'/- mit ein. Der Fragesteller will aber nur 0..9+() und das Leerzeichen erlauben. Das Leerzeichen habe ich vergessen. Die Zeichen / und - habe ich mit Absicht aus C_ERLAUBT entfernt.
Wie auch immer. Der Fragesteller kann es sich ja anpassen, wie er möchte.
und ich dachte schon, das sei vllt die Umkehrfunktion zu ChrW() für Unicode-Zeichen, die ich in xl noch nicht gefunden habe
(als Tabellenblattfunktion in Calc :
=Unicode("ꔘ"), =Unizeichen(42264) , das scheint xl nicht zu kennen. Oder gibts da was Neues?)
PS: ich hab übrigens weiter unten das entwertet als erweitert gelesen, daher das Mißverständnis!
Das kommt der Sache schon näher, Danke!
Aber ich habe noch 0 Ahnung von VB und Makros. Das wird also ein Abenteuer das zu durchschauen.