Once these characters are removed, we can use Excel’snumber format systemto format the numbers consistently.
We are also asking TEXTSPLIT to ignore any empty values that are generated.
This is important because some of these delimiters have no actual value between them.
you’re free to insert specific characters with any logic you like.
Note: leading zeros will be removed when the value is converted to a number.
If you need leading zeros, the simplest approach will be to concatenate manually as explained previously.
There are pros and cons to both approaches.
Finally, the “Special” telephone number format is applied to column D as explained above.
This pageexplains custom number formatsand with many examples.
you’re able to use this same trick tomake nested IF statements easier to readas well.
TEXTSPLIT can split text into rows or columns.
TEXTJOIN Function
The Excel TEXTJOIN functionconcatenatesmultiple values together with or without a delimiter.
