Phone number formatting tricks in Excel | XM Community
Skip to main content

Hi Everyone, 

I need to reformat moile phone numbers in excel so I can send a survey via SMS and for the life of me can’t figure out the right formula to get the right output with country code included.

 

I have one column with the country code, another with the mobile phone number and the yellow column what I need the output to be - add country code at start, remove the 0 at the start of the phone number and spacing.

I know how to combine two columns but can’t get rid of the 0 in column 2. I have tried playing with different cell number formats (special and custom) and can’t get the outcome I need.

I know this isn’t strictly Qualtrics question so any help would be appreciated!!

 

Country code Phone Output
61 0400 010 719 61400010719
61 0400 036 458 61400036458
61 0400 090 453  
61 0400 096 986  
61 0400 269 697

 

Hi @LibbyB ,

Assuming that “Country code ” is in Col A and  “Phone” in column B ,
Data in Country code is in format = 61
Data in Phone is in format =0400 010 719

You can enter below formulae in the output column   =A1&RIGHT(SUBSTITUTE(B1," ",""),LEN(SUBSTITUTE(B1," ",""))-1)  

It remove the leading zero from the phone and then concatenate Column A and B .

Hope this resolves your query😊!!

Amazing!!!

Thank you so much. I would never have got there. Clearly have some work in Excel to do.

Leave a Reply


Terms & Conditions