Excel tries to help... but sometimes it takes away the + from your country code in your phone numbers. Here's a guide on how to resolve this:
- Call your phone number column eg “Phone number country code text format”
- Make all your cells in that column into text format (select cells, right click, format cells, text, ok)
- Make a new column beside called “Phone number with country code formula”
- In top cell of column, enter formula =”+”&C2
- C2 is only an example, it should be cell reference number of the first cell in the “Phone number country code text format” column, i.e. the cell next door
- it’s basically saying put a plus & whatever is in cell C2
- this should result in the phone number with + at the start
- Select the cell with this shiny new correct phone number with a + in it, then grab the plus sign in the bottom right and drag it down to the bottom of your column. This should fill out the whole column with the correct numbers.
- Finally, create a column beside it called “Phone number” — you want the phone numbers in here, in text format, so you can safely delete the other two columns and not have messy data with extra columns
- Copy all the phone number cells from the column called “Phone number with country code formula”
- Go to edit > paste special > values > ok. This should result in “Phone number” column containing all the correct numbers. You may get a little pop up warning saying “number stored as text” — that’s fine, you want that because it means you get to keep the + at the start.
- You can now safely delete the two original columns “Phone number country code text format” and “Phone number with country code formula”