Generieke formule
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
Samenvatting
Een manier om telefoonnummers op te schonen en opnieuw te formatteren, is door alle vreemde tekens te verwijderen en vervolgens het ingebouwde telefoonnummerformaat van Excel toe te passen.
Deze formule hierboven gebruikt een reeks geneste SUBSTITUTE-functies om spaties, koppeltekens, punten, haakjes en komma's te verwijderen.
U moet de daadwerkelijke vervangingen aanpassen aan uw gegevens.
Uitleg
De formule werkt van binnen naar buiten, waarbij elke VERVANGING één teken verwijdert.
De binnenste VERVANGING verwijdert de linker haakjes, en het resultaat wordt overgedragen aan de volgende VERVANGING, die de rechter haakjes verwijdert, enzovoort.
Telkens wanneer u de functie SUBSTITUTE gebruikt, is het resultaat tekst. Omdat u geen getalnotatie op tekst kunt toepassen, moeten we de tekst naar een getal converteren. Een manier om dat te doen is door nul (+0) toe te voegen, waardoor getallen in tekstformaat automatisch worden geconverteerd naar getallen in numeriek formaat.
Ten slotte wordt het "speciale" telefoonnummerformaat toegepast (kolom D).
Deze pagina geeft uitleg over aangepaste getalnotaties en met veel voorbeelden.
Witruimtetruc voor betere leesbaarheid
Bij het nesten van meerdere functies kan het moeilijk zijn om de formule te lezen en alle haakjes in evenwicht te houden. Excel geeft niet om extra witruimte in een formule, dus u kunt regeleinden in de formule toevoegen om de formule leesbaarder te maken. De bovenstaande formule kan bijvoorbeeld als volgt worden geschreven:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Merk op dat de cel in het midden verschijnt, met functienamen erboven en vervangingen eronder. Dit maakt de formule niet alleen gemakkelijker te lezen, maar het maakt het ook gemakkelijker om vervangingen toe te voegen en te verwijderen.
U kunt dezelfde truc gebruiken om geneste IF-instructies ook gemakkelijker leesbaar te maken.
