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 [phone] [phone] Tel.: [phone] Telefon: 434321 / 4234

Ich will immer nur die oben genannten Zeichen erhalten, alles andere entfernen.

Answer 1

[Iamiam]

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!

Comments

[Iamiam]

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!

Answer 2

[mrDoctor]

Versuch es mit "Suchen und Ersetzen". 

Tastenbefehl: CTRL + F

Comments

[k3kz0r]

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.

Answer 3

[Zebulon361]

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 :)

Answer 4

[iMPerFekTioN]

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

Woher ich das weiß: Berufserfahrung – Eigenständiges lernen während und nach meiner Ausbildung

Answer 5

[Suboptimierer]

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

Comments

[k3kz0r]

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.

[Iamiam]

@k3kz0r

DH!: die Positiv-Liste in C_ERLAUBT ist eine gute Idee. Ich dachte, das muss man mit einer Case-Schleife machen, mit der ich aber regelmäßig Schwierigkeiten bekomme (vllt müsste ich das nur mehr üben, die Katze beißt sich in den Schwanz!)

[Suboptimierer]

@Iamiam

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.

[Iamiam]

@Iamiam

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

[Suboptimierer]

@Iamiam

Das verstehe ich nicht. Das '/-" ist ein mit Absicht entwerteter Code. Ich habe die Zeichen in einem Test zugelassen.

[Iamiam]

@Suboptimierer

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!

[Suboptimierer]

@Iamiam

Ja, Ord ist Pascal. Habe es verwechselt.

[Iamiam]

@Suboptimierer

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

[Suboptimierer]

@Iamiam

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.

[Iamiam]

@Suboptimierer

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!