SOLVED

Format change - How to place "-" between numbers

Copper Contributor

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. 

Woogii1475_0-1688289079713.png

 

Please refer to the attached image and suggest ways in which I can quickly change the formatting using one or more functions.

 

 

 

 

3 Replies

@chris7749 

=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.

best response confirmed by chris7749 (Copper Contributor)
Solution

@chris7749 

Perhaps this one:

=TEXT(B2*1,"000-0000-0000")
=IF(LEN(B2)=11,WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~(\d{3})(\d{4})(\d{4})~\1-\2-\3~" & B2),B2)
1 best response

Accepted Solutions
best response confirmed by chris7749 (Copper Contributor)
Solution

@chris7749 

Perhaps this one:

=TEXT(B2*1,"000-0000-0000")

View solution in original post