Telefonnummer trennen in Vorwahl und Rufnummer

Forumthread: Telefonnummer trennen in Vorwahl und Rufnummer

15.04.2015 18:24:38

Werner Perouse

Hallo Forum,
ich habe 2 verschiedene Tabellen. Die eine Tabelle ist eine "Rufliste" aus meiner Telefon-Anlage, die zweite eine Liste aller (derzeitigen) deutschen Vorwahlen. Diese 2. Tabelle hat 3 Spalten (Vorwahl, Länge der Vorwahl und Ortsname).
Weil in der Rufliste die Telefon-Nummer als ganze Zahl angegeben ist, möchte ich nun versuchen, diese Ziffernfolge in Vorwahl und Rufnummer aufzuteilen. Leider gibt es in Deutschland kein einheitliches System für Vorwahlen; die können 3 (z. B. "030 für Berlin) oder mehr (maximal 6) Stellen lang sein.
Ich könnte zwar die 2. Tabelle Zeile für Zeile "abarbeiten", und prüfen, ob die dort enthaltene Vorwahl mit dem Beginn meiner Telefon-Nummer übereinstimmt. Allerdings hat diese Tabelle mehr als 5.000 Einträge. Alleine 1.500 Einträge für Vorwahlen, welche mit 03 beginnen. Und die Nummern im Süden fangen mit 07 bzw. 08 an. Da wird die Suche einige Zeit in Anspruch nehmen. Bestimmt gibt es bessere Möglichkeiten.
Wer kann mir ein Beispiel nennen, um aus einer x-beliebigen Telefon-Nummer mit Hilfe der 2. Tabelle Vorwahl und Rufnummer zu trennen.
Grüße aus dem Schwabenland
Werner

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Mit der kompletten Vorwahlliste geht es so:

15.04.2015 19:23:52

lupo1

A2: '030123456789 inländische Telefonnummer mit Vorwahl und ohne Trenner als Text
B2: =SVERWEIS(A2;$E$2:$E$5001;1) daraus die Vorwahl
C2: =WECHSELN(" "&A2;" "&B2;) daraus der Anschluss
E2: '0171 inländische Vorwahl als Text. Muss aufsteigend sortiert sein!
___________________________________________________________________
MfG Lupo - 11.10.2012: {MSO 2000 SP3;Win 7} - 28.02.2013: {MSO 2010;Win 8.1} (beide vorhanden; meistens 2010 verwendet)

AW: Mit der kompletten Vorwahlliste geht es so:

15.04.2015 19:59:45

Werner Perouse

Hallo Lupo,
danke für die rasche Antwort. Dein Vorschlag sieht aber nicht nach VBA aus. Außerdem stehen Telefon-Nummern und Vorwahlen in 2 unterschiedlichen Tabellen.
Gruß
Werner

Excel ist zuerst einmal Formel und erst dann VBA

16.04.2015 01:51:34

lupo1

VBA hast Du übrigens nicht spezifiziert; überhaupt wundere ich mich, dass das in Deinem Beitrag fehlt, ohne welche man ihn hier bei Herber doch eigentlich gar nicht absenden kann (?).
Die Aufteilung auf 2 Tabellen leistet der Frager basiswissend normalerweise selbst:
- ersetze mein obiges $E$2:$E$5001 in der zweiten Formel durch Tabelle2!E$2:E$5001
- die Vorwahlliste beginnt dann entsprechend in Tabelle2!E2 (ich schreibe es lieber dazu)
___________________________________________________________________
MfG Lupo - 11.10.2012: {MSO 2000 SP3;Win 7} - 28.02.2013: {MSO 2010;Win 8.1} (beide vorhanden; meistens 2010 verwendet)

AW: Telefonnummer trennen in Vorwahl mit UDF

16.04.2015 09:46:41

Daniel

Hi
mit Formeln allein wird das etwas aufwendiger.
kopiere dir mal folgenden Code in ein allgemeines Modul:

Function VorwahlTrennen(TelefonNr As String, _
VorwahlListe As Range, _
Optional MinVW As Long = 3, _
Optional MaxVW As Long = 6) As String
Dim VW_AusListe As String
Dim VW_TelNr As String
Dim i As Long
For i = MaxVW To MinVW Step -1
VW_TelNr = Left(TelefonNr, i)
VW_AusListe = WorksheetFunction.VLookup(VW_TelNr, VorwahlListe, 1, 1)
If VW_TelNr = VW_AusListe Then Exit For
Next
If i < MinVW Then
VorwahlTrennen = TelefonNr
Else
VorwahlTrennen = "+49 (" & Mid(TelefonNr, 2, i - 1) & ") " & Mid(TelefonNr, i + 1)
End If
End Function

dir steht jetzt in Excel die Funktion VorwahlTrennen zur verfügung.
1. Parameter: die Telefonnummer
2. Parameter: die Spalte mit den Vorwahlnummern
3. Paremeter: die mindestlänge der Vorwahl
4. Paremeter: die maximallänge der Vorwahl
3. und 4. Paremeter sind optional und auf 3 und 6 voreingestellt.
Ausgabe erfolgt dann in der Form: +49 (Vorwahl) Durchwahl
(aber das kannst du ja selber leicht ändern, wenns anders aussehen soll)
voraussetzung ist, dass die Telefonnummer und Vorwahl als Text mit führender 0 vorliegen.
die Liste der Vorwahlnummern muss aufsteigend sortiert sein, mit der Option "Zahlen und als Text formatierte Zahlen getrennt sortieren". Bei Sortierung der Texte als Zahl kommt der SVerweis durcheinander (und ich nehme hier aufgrund der Datenmenge die Variante die sortierte Daten benötigt, weil sie schneller ist).
Gruß Daniel

AW: Telefonnummer trennen in Vorwahl mit UDF

16.04.2015 20:22:40

Werner Perouse

Hallo Daniel,
vielen Dank für die komplette Function. Nach ein paar "persönlichen" Anpassungen habe ich diese Funktion nun erfolgreich im Einsatz.
Kleiner Hinweis zur Vollständigkeit: ich musste noch eine "on Error goto xxx"-Routine einbauen, weil z. B. die 0800 nicht in meiner Vorwahl-Tabelle ist. Und auch meine Gespräche mit Österreich (0043) haben Probleme gemacht. Alles gelöst.
Es ist immer schön, wenn man im Forum innerhalb kurzer Zeit brauchbare Antworten bekommt.
Gruß und Dank.
Werner (der aus dem Schwabenland)

Infobox / Tutorial

Telefonnummer trennen in Vorwahl und Rufnummer

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um eine Telefonnummer in Vorwahl und Rufnummer zu trennen, kannst du die folgenden Schritte in Excel ausführen. Diese Anleitung geht davon aus, dass du eine vollständige Liste der deutschen Vorwahlen hast.

Daten vorbereiten: Stelle sicher, dass du zwei Tabellen hast: eine mit den Telefonnummern (z.B. in Spalte A) und eine mit den Vorwahlen (z.B. in Spalte E).
Vorwahl extrahieren:
- Angenommen, deine Telefonnummer steht in Zelle A2. In Zelle B2 kannst du folgende Formel verwenden:
```
=SVERWEIS(A2;Tabelle2!E$2:E$5001;1;FALSCH)
```
- Diese Formel sucht die Vorwahl in der Liste der Vorwahlen.
Rufnummer extrahieren:
- In Zelle C2 kannst du dann die folgende Formel verwenden, um die Rufnummer zu erhalten:
```
=WECHSELN(" "&A2;" "&B2;"")
```
- Diese Formel ersetzt die Vorwahl in der Telefonnummer durch nichts, sodass nur die Rufnummer übrig bleibt.
Ergebnis überprüfen: Überprüfe die Ergebnisse in Spalte B und C, um sicherzustellen, dass die Vorwahlen und Rufnummern korrekt extrahiert wurden.

Häufige Fehler und Lösungen

Fehler: Keine Übereinstimmung gefunden: Wenn die Formel in B2 einen Fehler zurückgibt, stelle sicher, dass die Vorwahlliste korrekt sortiert ist und dass die Telefonnummern als Text vorliegen.
Fehler: Falsches Format: Wenn die Telefonnummern nicht im richtigen Format angezeigt werden, stelle sicher, dass du die Spalte als Text formatierst. Dies hilft, Probleme mit führenden Nullen zu vermeiden.

Alternative Methoden

Eine alternative Methode zur Trennung von Vorwahl und Rufnummer ist die Verwendung einer benutzerdefinierten Funktion in VBA. Hier ist ein Beispiel:

Öffne das VBA-Editor-Fenster (Alt + F11).

Füge ein neues Modul ein und kopiere den folgenden Code hinein:

Function VorwahlTrennen(TelefonNr As String, VorwahlListe As Range, Optional MinVW As Long = 3, Optional MaxVW As Long = 6) As String
   Dim VW_AusListe As String
   Dim VW_TelNr As String
   Dim i As Long
   For i = MaxVW To MinVW Step -1
       VW_TelNr = Left(TelefonNr, i)
       VW_AusListe = WorksheetFunction.VLookup(VW_TelNr, VorwahlListe, 1, 1)
       If VW_TelNr = VW_AusListe Then Exit For
   Next
   If i < MinVW Then
       VorwahlTrennen = TelefonNr
   Else
       VorwahlTrennen = "+49 (" & Mid(TelefonNr, 2, i - 1) & ") " & Mid(TelefonNr, i + 1)
   End If
End Function

Diese Funktion kann dann in Excel verwendet werden, um eine Telefonnummer im Format "+49 (Vorwahl) Durchwahl" zu formatieren.

Praktische Beispiele

Beispiel 1: Für die Telefonnummer 0301234567 wird die Vorwahl 030 und die Rufnummer 1234567 extrahiert.
Beispiel 2: Bei der Nummer 01711234567 erhältst du die Vorwahl 0171 und die Rufnummer 1234567.
Beispiel 3: Eine internationale Nummer wie 0043123456789 könnte als +43 (12) 3456789 formatiert werden, wenn du die ländervorwahl berücksichtigst.

Tipps für Profis

VBA verwenden: Wenn du oft mit Telefonnummern arbeitest, kann die Verwendung einer VBA-Funktion die Verarbeitung erheblich beschleunigen.
Datenvalidierung: Stelle sicher, dass deine Vorwahlliste aktuell und korrekt ist, um Fehler zu minimieren.
Formatierung: Achte darauf, dass die Spalten für Telefonnummern als Text formatiert sind, um Komplikationen mit führenden Nullen zu vermeiden.

FAQ: Häufige Fragen

1. Was ist eine ländervorwahl?
Die Ländervorwahl ist der Code, der benötigt wird, um ein Telefonat in ein anderes Land zu tätigen, z.B. 0043 für Österreich.

2. Wie kann ich die Telefonnummern im internationalen Format anzeigen?
Verwende die Funktion VorwahlTrennen aus VBA, um die Telefonnummern im Format "+49 (Vorwahl) Durchwahl" anzuzeigen.

3. Was mache ich, wenn ich eine Vorwahl nicht finde?
Wenn du eine Vorwahl nicht findest, stelle sicher, dass deine Vorwahlliste vollständig und korrekt ist. Du kannst auch eine Fehlerbehandlungsroutine in deine VBA-Funktion einfügen.

Beliebteste Forumthreads (12 Monate)

Telefonnummer trennen in Vorwahl und Rufnummer

Forumthread: Telefonnummer trennen in Vorwahl und Rufnummer

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Infobox / Tutorial

Telefonnummer trennen in Vorwahl und Rufnummer

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread