Forum Discussion

kkoperator's avatar
kkoperator
Copper Contributor
Jan 28, 2022
Solved

[GELÖST] -- Dublikate in Telefonnummern suchen und löschen

Ich habe eine Spalte mit Telefonnummern, die ich gern auf Duplikate prüfen möchte. Ich habe bereits die Excel-Funktion "Duplikate entfernen" genutzt. Ich möchte dennoch prüfen ob auch alle Duplikat...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 13, 2022

    kkoperator The article in the link is a bit unclear. The Phone Number type appears when you select Special and then change the Language/location to e.g. English (US). Several other languages also show this option, but not all. Select "Phone Number" and then select Custom. Now you see the custom format for a US phone number, and create your own from it.

    Note: These screenshots were taken on a Mac but it looks similar on the PC version.

bosinander's avatar
bosinander
Steel Contributor
Jan 28, 2022

Hi kkoperator 

I would purge all characters but figures and then compare the output, column B.

Allowed characters in B3.

Since you have a country prefix, it have to be omitted like column E.

It's an old solution that may be done more pretty and easier to read today but it could probably help you as is.

=TEXTJOIN("";0;IFERROR(MID($B$3;FIND(MID($A5;COLUMN(OFFSET($A5;0;0;1;LEN($A5)));1);$B$3);1);""))

Duplicates are colored by Home tab:

 

kkoperator's avatar
kkoperator
Copper Contributor
May 13, 2022
The problem is, if I omit all characters except numbers, then (including spaces), then for example 0049 becomes 49.... and I don't see anymore if it was a prefix for 04921 or a country code +49.