If people have submitted their numbers through a Google Form, it’s very likely that you end up with a bunch of different phone number formats. Therefore we will cover some basic tips and tricks to getting your list of phone numbers right in Google Sheets. Our goal is to efficiently modify every number to have a country code and nice look. More information about country codes can be found in this great Wikipedia article.
Here are some examples of number formats that all are valid to send sms to with the Cloudcom SMS add-on.
| USA (+1) | Sweden (+46) |
|
|
In the next few sections I will give you some examples of formulas that can help you clean up the phone numbers to become consistent in the spreadsheet.
Remove non-digits
In order to make your numbers more consistent in the spreadsheet you can easily remove all non digits (bracks, dashes, spaces etc.) from a cell using the REGEXREPLACE formula.
Concatenate country code
One approach to add country codes is by using the formula CONCAT, which concatenates texts into a new cell.
Conditional formulas
Suppose some numbers in a column might include country code and others not. To deal with this case we can use the IF and LEFT formula. What the formula below does is to first check whether the first two digits equals the right country code. If this condition is satisfied nothing more is done, otherwise the formula will concatenate the right country code.
