How to format mobile phone numbers in Excel

Follow our three-step guide to excel-lently format your phone numbers. This is super useful for when you’re uploading your contacts into FireText’s SMS platform from a CSV file.

Step 1: Clear out the data you don’t need

Firstly, remove anything from your spreadsheet that you don’t want to be uploaded into the FireText platform.

If you’re doing this, be sure to actually ‘delete’ columns instead of simply hiding them. And if you’re removing unnecessary information ready to upload to FireText, always keep a backup file!

Step 2: Place data in first 6 columns

You have the option to upload your recipients’ name, number and 3 custom fields, so only the first 6 columns will be uploaded into the platform.

You can pick and choose from these options which data you would like to upload, but obviously mobile numbers are essential.

If you only want to upload numbers, that’s absolutely fine. Again, just make sure your list is placed within one of the first six columns. Just like the example below.

Step 3: Format your phone numbers correctly

When saving an Excel document as a CSV file, it will save it exactly how it looks.

Although Excel is great with numbers, when it comes to phone numbers it tends to struggle and will often shorten it – so you may see an E+10 or E+11 at the end.

Because this is how the CSV will save it, the FireText platform is clever enough to detect that these numbers aren’t correct – preventing you from uploading them.

So you need to ensure that the numbers are formatted correctly. To do this:

  • Select the column with your numbers in it and right-click, then select ‘Format Cells’.
  • You will see a pop-up box, with several options down the left-hand side – you want to hit the ‘Number’ tab.
  • Then where it says ‘Decimal places’, you want to set this to 0. As soon as you hit ‘OK’ your numbers will all be formatted correctly and ready to go.

Now go ahead and upload your contacts

Once those final checks are done and you’re all ready to save, simply select ‘save as’ and hit the ‘CSV (Comma delimited) (*.csv)’ option.

Now you’re ready to upload them into the platform – and we have an awesome guide to uploading contacts that will explain step by step how to do this.

Sending to international numbers? Your FireText account is set up as default to send to UK numbers only. If you have an international account enabled with us, follow our formatting guidance below.

Key considerations when uploading to FireText

The platform is pretty flexible with formatting

As a note, excel may remove the first ‘0’. Don’t panic if this happens as the FireText platform can recognise any numbers uploaded in the format of 07, 447 or just 7***.

The system will also remove any spaces, or other characters too. (This includes the ‘+’ in ‘+44’) So, if you have a hyphen or even some of the numbers in brackets, the platform will still upload these as numbers.

One Excel sheet does plenty

A few last things to double-check is the number of sheets on your document. Ideally, you only want one. However, if there is more than one, you need to make sure it’s the first tab.

Sending internationally: converting numbers

As default, FireText accounts are set to send to UK numbers only. If do you have an international account enabled with us, you need to make sure all of your numbers begin with the right area code.

This is important. You’ll notice that when you enter your numbers into an Excel spreadsheet, Excel automatically removes the first zero. For a UK account, this isn’t a problem, as FireText will still count them as a valid mobile number.

However, an international account will count these as Russian mobile numbers (which begin with 7) if we don’t format them first.

We’ve got an easy formula you can apply to your spreadsheet.

  • Make sure that all mobile numbers are running down one column
  • Click the blank box in the column right of the first number (if you have more data alongside this, right-click the entire column and select ‘insert column to the right’).
  • In this blank box enter =CONCATENATE(44,A1) and hit enter
  • Grab the corner of this box and drag down throughout all of the numbers going down the original column
  • This will fill the new column with all of the mobile numbers, now including the 44 country code in front of them
  • Now copy this column and paste the values (paste special >> values) over the old column
  • Save the file in a CSV format and you’re ready to upload your contacts to FireText!

Now you’re ready to craft your message and send out to your contacts. For top tips, check out our ultimate guide to SMS marketing.

FireText provide SMS Marketing for your business.
Add SMS to your marketing mix today – find out more.