Jul 02 2023 02:25 AM
I have a list of phone numbers that are not in a consistent format, and I want them all to have the same format, which is with a hyphen "-" between the numbers. It should be shown as the following example: 010-0000-0000. Note that some of the numbers are already in this format.
Please refer to the attached image and suggest ways in which I can quickly change the formatting using one or more functions.
Jul 02 2023 09:17 AM
=IF(ISNUMBER(FIND("-",B2)),B2,IF(LEN(B2)=12,B2,CONCATENATE(LEFT(B2,3),"-",MID(B2,4,4),"-",RIGHT(B2,4))))
With this formula you would not have to get a double "-" if they already exist in some numbers.
Paste the formula into A2 or C2 and just drag it down.
My answers are voluntary and without guarantee! 🙂
Hope this will help you.
Jul 02 2023 10:08 AM
SolutionJul 02 2023 04:24 PM
Jul 02 2023 10:08 AM
Solution