Telefonnummern vergleichen und formatieren

04.02.2025 15:23:06

Pseudotussi

Hallo,
ich habe eine Liste mit diversen Telefonnummern, die ich für Ihre weitere Verwendung in anderen Systemen aufbereiten muss. Die Arbeitsmappe findet Ihr unter: https://www.herber.de/bbs/user/175467.xlsx

In Spalte A befinden sich diverse Nummern aus dem In- und Ausland.

Spalte B:
- hier sollen die ersten Nummern aus Spalte A mit der Spalte J verglichen werden (Beim Vergleich sollten die führenden Nullen ignoriert werden).
> sind diese identisch, sollen vor der Nummer 2 Nullen gesetzt werden
- sind diese nicht identisch, soll mit Spalte M verglichen werden
> sind die identisch, soll vor der Nummer ein 0049 gesetzt werden
- ist bereits in der Nummer eine 49 als führende Zahl gegeben, dann sollen zwei Nullen davor geschrieben werden
- sind die führenden Ziffern weder in Spalte M, noch in Spalte J zu finden, soll die Nummer aus Spalte A ausgegeben werden

Spalte C
- hier sollen nur die deutschen Nummern inkl. einer Null vor der Vorwahl ausgewiesen werden. Vergleich mit Spalte M

Spalte D
- hier sollen nur die ausländischen Nummer inkl. zwei führenden Nullen erscheinen. Vergleich mit Spalte J

Spalte E
- egal welche Nummer, nach der Länderkennung bzw. der Ortsvorwahl soll ein Trennstrich stehen.

Da die Nummern unterschiedliche Längen haben, komme ich nicht weiter.
Ich habe es schon mit Verweis, Vergleich, Index, Links etc. versucht, sterbe aber unterwegs leider immer wieder ab und gerate an meine Grenzen. Da ich an der Datei nun schon seit voriger Woche sitze, hoffe ich nun, dass Ihr mir weiterhelfen könnt.

Vielen lieben Dank im voraus.

AW: Telefonnummern vergleichen und formatieren

04.02.2025 19:45:53

{Boris}

Hi,

entferne als erstes mal das Zeichen 160 aus Deinen Vorwahlen (hab es jetzt erst mal nur bei den internationalen geprüft).

So ein Zeichen mit Strg + c kopieren (z.B. aus der Afghanistan-Vorwahl 0093 am Ende), dann Spalte J markieren, Strg+f, Suchen nach: Strg + v, Ersetzen durch: Leer lassen, Alle ersetzen. Waren bei mir 190 Ersetzungen. Anschließend Spalte J als Standard formatieren. Dann sind alles erst mal (wieder) Zahlen, die man zum Vergleich heranziehen kann.
Später kann man sie dann leicht wieder zur vollständigen Vorwahl machen mit:

="00"&Tabelle2[@Vorwahl]

Bin jetzt erst mal weg. Mehr vielleicht später.

VG, Boris

AW: Telefonnummern vergleichen und formatieren

04.02.2025 19:48:28

Yal

Hallo Ines,

bei der komplexe Vorhaben würde ich vorschlagen, das Problem in kleine Teil zu zerlegen:
- zuerst erkennen, ob ein internationaler Vorwahl vorliegt, und welche.
- dito national
- wenn man das hat, kann man darauf weiterbasteln.

Ich habe deine beiden Tabelle in "Vorwahl_int" und "Vorwahl_DE" umbenannt: auf einer Zelle der Tabelle positionieren und im Menü "Tabellenentwurf" die Tabelle umbenennen (wenn nicht, den Code unten dementsprechend anpassen).
In Vorwahl_int sind die Nummer nicht "sauber": es gibt führenden Nullen und am Ende teilweise einen "Internet Leerzeichen" (Code 255)
Mit der Formel
=GLÄTTEN ( WECHSELN ( J2; ZEICHEN(255); "" ))
und das Ergebnis auf das original als Wert einfügen, werden die Zahlen bereinigt (wobei der Code die Unreinheiten auch beseitigt).

Da Du leider Excel 2013 verwendest*, kann man nicht die aktuelle, starke Excel365-Formel verwenden. Ich schlage daher eine UDF-Lösung vor: eine in VBA definierte Excel-Funktion, die man in Excel wie eine normale Formel verwenden kann.
(*: was eigentlich nicht sein kann, weil Du Tabelle verwendest, die in Excel 2013 noch nicht gab)

In VBA-Editor (mit Alt+F11 aufrufen), füge einen Modul ein: Menü "Einfügen", "Modul"
In diesem Modul, füge folgenden Code ein:

'Unter Anbindung von:

'(in VBA-Editor, Menü "Extras", "Verweise…", Bibliothek anhaken)

'Microsoft Scripting Runtime



Private Dic_int As Object

Private Dic_DE As Object



Private Sub Dic_int_herstellen()

Dim R As ListRow

Dim Sch As String



'international

    Set Dic_int = CreateObject("Scripting.Dictionary")

    For Each R In Range("Vorwahl_int").ListObject.ListRows 'in deiner Version noch Range("Tabelle1")

        Sch = Trim(Replace(R.Range(1).Value, Chr(255), ""))

        Dic_int(Sch) = R.Range(2)

    Next

End Sub



Private Sub Dic_DE_herstellen()

Dim R As ListRow

Dim Sch As String



'lokal

    Set Dic_DE = CreateObject("Scripting.Dictionary")

    For Each R In Range("Vorwahl_DE").ListObject.ListRows 'in deiner Version noch Range("Tabelle2")

        Sch = Trim(Replace(R.Range(1).Value, Chr(255), ""))

        Dic_DE(Sch) = R.Range(2).Value

    Next

End Sub



Public Function NrInfo(ByVal Target)

Dim i As Integer

    NrInfo = "#NV"

    If Dic_int Is Nothing Then Dic_int_herstellen

    If Dic_DE Is Nothing Then Dic_DE_herstellen

    If TypeOf Target Is Range Then Target = Target.Cells(1).Value

    Target = Trim(CStr(Target))

'chr 255 (internet Leerzeichen)

    Do While Left(Target, 1) = Chr(255)

        Target = Mid(Target, 2)

    Loop

'Nullen

    Do While Left(Target, 1) = "0"

        Target = Mid(Target, 2)

    Loop

'vergleich international

    For i = 2 To 4

        If Dic_int.Exists(Left(Target, i)) Then

            NrInfo = Array("int", Left(Target, i), Dic_int(Left(Target, i)))

            Exit Function

        End If

    Next

'vergleich international

    For i = 2 To 4

        If Dic_DE.Exists(Left(Target, i)) Then

            NrInfo = Array("DE", Left(Target, i), Dic_DE(Left(Target, i)))

            Exit Function

        End If

    Next

End Function

Im Blatt kannst Du dann in Zelle B8 die Formel
=NrInfo(A8)
einsetzen.

Eventuell müsstest Du versionbedingt mit Shift+Strg+Enter abschliessen. Dann die Zelle B8 bis D8 erweitern (mit dem Maus am rechten, unteren Ecke fassen und ziehen).

VG
Yal

AW: Telefonnummern vergleichen und formatieren

04.02.2025 20:06:21

Yal

Moin,

auch machbar ist die Formel
=WENNFEHLER(SVERWEIS(--LINKS(A8;4);Vorwahl_int;2;0);WENNFEHLER(SVERWEIS(--LINKS(A8;3);Vorwahl_int;2;0);WENNFEHLER(SVERWEIS(--LINKS(A8;2);Vorwahl_int;2;0);WENNFEHLER(SVERWEIS(--LINKS(A8;4);Vorwahl_DE;2;0);WENNFEHLER(SVERWEIS(--LINKS(A8;3);Vorwahl_DE;2;0);WENNFEHLER(SVERWEIS(--LINKS(A8;2);Vorwahl_DE;2;0);"ERR"))))))

Aber die Formel WennFehler gibt es erst ab Version 2016

Hier auch vorab die Zahlen um das Zeichen 160 und die führenden Nullen wegmachen (Es darf "0093" durch das Format angezeigt werden, aber dahinter muss der Zahl 93 stehen, kein Text).
Und die Liste muss anhand der Zahl sortiert sein, sonst kommt Sverweis gar nicht klar.

VG
Yal

AW: gleiche DE Städte und Länder Vorwahlen!!

04.02.2025 22:37:21

Piet

Hallo an alle

ich habe mich mal mit einer VBA Lösung beschäftigt, dabei sind mir auch Fehler im Ländercode aufgefallen.

Bei der Ländervorwahl 49 für Deutschland ist zu beachten, das es 54 Städte gibt deren Vorwahl -auch- mit 49 beginnt!
Interessant finde ich, das 21 Länder die gleiche Vorwahl wie DE Städte haben, und 142 Länder Vorwahlen teilweise mit DE Städten übereinstimmen. z.B. 0020 für Ägypten, kann leicht mit vielen NRW Städten kollidieren!
https://www.herber.de/bbs/user/175486.xlsx

Das ist der Grund warum mein Makro noch nicht fertig ist, denn die interessante Frage ist ja: - Was wertet man zuerst aus??
Zuerst die 49 als DE Vorwahl, dann die Städte Vorwahlen? - Oder lieber zuerst die Länder Vorwahlen?
Wie seht ihr die Aufgabe?? Die scheint doch recht kompliziert zu sein, denn es kann Überschneidungen geben.

mfgPiet

WENNFEHLER...

04.02.2025 20:31:28

{Boris}

Hi Yal,

Aber die Formel WennFehler gibt es erst ab Version 2016

...gibt es seit 2010.

VG, Boris

AW: WENNFEHLER...

04.02.2025 20:45:03

BoskoBiati

Hallo,

kann mir jemand erklären, wie man feststellen will, ob eine Nummer eine Inlandsvorwahl oder Auslandsvorwahl hat?
Wenn ich zuhause am Rechner bin werde ich gerne mal ein paar Beispiele dazu bringen.

Gruß

Edgar

AW: WENNFEHLER...

04.02.2025 21:03:13

Yal

Hallo Edgar,

in der Tat: 21 Überschneidungen:
Userbild

VG
Yal

AW: WENNFEHLER...

04.02.2025 20:58:02

Yal

Hmm... Ich habe die Information von
https://support.microsoft.com/de-de/office/wennfehler-funktion-c526fd07-caeb-47b8-8bb6-63f3e417f611
unter "Applies to".

Heb selber keine Erinnerung an Excel 2010.

VG
Yal

Ich habe jetzt...

04.02.2025 21:07:00

{Boris}

Hi Yal,

..den gesamten Text Deines Links durchgelesen - und nirgendwo was von "Applies to" to gelesen....?!

WENNFEHLER gibt es sehr sicher seit 2010.

VG Boris

AW: Ich habe jetzt...

04.02.2025 21:13:42

Yal

Ganz oben, ganz klein direkt unter "WENNFEHLER (Funktion)"

Ich stelle aber gerade fest, dass Microsoft (wahrscheinlich weil nicht mehr gewartet) grundsätzlich nichts älteres als Excel 2016 anzeigt.
Hier am Beispiel mit SUMME, die es mit Sicherheit seit Excel 1.0 gibt:
https://support.microsoft.com/de-de/office/summe-funktion-043e1c7d-7726-4e80-8f32-07b23e057f89

Also dann WENNFEHLER gab es wohl schon seit Excel 2010 (Sorry, ich werde es nicht näher nachprüfen ;-)

VG
Yal

Jetzt hab ich es....

04.02.2025 21:24:17

{Boris}

Hi Yal,

....hatte es nicht aufgeklappt.
Fakt: WENNFEHLER gibt es seit 2010. Die MS-Hilfe wird eh immer mehr schlampig.

VG, Boris

AW: Ich habe jetzt...

04.02.2025 21:14:50

JoWE

....steht doch unmittelbar am Anfang....

Telefonnummern vergleichen und formatieren

Forumthread: Telefonnummern vergleichen und formatieren

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Telefonnummern vergleichen und formatieren

Forumthread: Telefonnummern vergleichen und formatieren

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread