Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1420to1424
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Telefonnummer trennen in Vorwahl und Rufnummer

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

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

Betreff
Datum
Anwender
Anzeige
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)

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

Anzeige
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

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

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige