A common question we get asked is how to format mobile numbers correctly before uploading into our Online SMS product textburst. So to help here is some straight forward instructions.
Summarised Version
If you are competent with excel just follow the 4 steps below. For a more in-depth explanation see further down this post.
1. Remove column headers
2. Remove any spaces in the numbers.
3. Remove 0 and prefix the numbers with the country code.
4. Save as a CSV File
That’s it you’re done & ready to upload to textburst!
Detailed Version
1. Remove column headers
- Delete the entire row containing the headers, right click on that row’s number and choose delete
2. Remove any spaces in the numbers
- Left click on column letter to highlight and press Ctrl + F which will bring up a ‘Find and Replace’ box.
- Click on Replace tab.
- Enter a space in the ‘Find what’ box.
- Leave ‘Replace with’ box empty.
- Click on Replace All button.
3. Remove 0 and prefix the numbers with the country code.
- Right click on the column with the numbers in and click format cells, in the number tab click number in category and select 0 decimal places.
- Enter 44 (or appropriate country code) in the first cell of the next blank column right click on the cell and select copy then highlight the cells below that you are going to copy in to and click paste.
- In the first cell of the next column, enter the formula =CONCATENATE(D1,C1), you may need to alter the column letters depending on what columns your numbers are in. Copy this down to replicate the formula but change the cell refs in it each time, click on the cell and drag the cross in the bottom right down to highlight the cells you are copying to.
- Copy the column with the formula, left click the column letter, right click and choose copy, then paste special into the next column, right click the column letter and choose Paste Special, select the Values option.
- Now Delete the previous columns (C, D & E), left click on the column letter to highlight, right click and choose delete.
4. Save as a CSV File
- Go to ‘Save As’ and save as a ‘CSV comma delimited’
Good guide thanks!
It would be easier to upload an Outlook pst file, or a collection of Vcards.
I missed this, uploaded a load of contacts this morning, and all this stuff happened automatically.
My format was 07831 412088, and by the time they arrived on my dashboard, they were in the format 44 7831 412 088.
Does that mean all the above work is now unnecessary? I assume so…
Hi Robert,
You are correct that excel will upload with the leading zero and translate into a UK format number.
However, we have advised the full process above because there are instances where excel loses the leading zero when you save the file and hence the upload then fails.
Also, for our international customers “44″ may not be the correct prefix so those customers too need to follow the full process above.
Hi,
Any way to upload contacts from AD ?
Yann
Hi Yann,
There are a couple of options
1) If you can download your data to a .csv file then you can upload as per the above into textburst.
2) If you use our OMS (Outlook mobile service) product then you can send to any mobile phone numbers in your address book (or Active Directory)
Hope that helps.
Dawn