Formatting Mobile Numbers for Data Imports

The E.164 format is a globally recognised standard format for phone numbers. This is a required format to enable SMS functionality within Jomablue. The format is as follows:

• + (plus symbol)
• International Country Calling Code
• Service number
• No spaces, alphas or special characters

Mobile numbers are required to be in E.164 format when importing people into Jomablue. If you are using Jomablue registration, the formatting is automatically applied.

Conversion to E.164 examples:

Australian number: 0421 221 001
Becomes: +61421221001

US Number: (415) 555-2671
Becomes: +14155552671

UK number: 020 7183 8750
Becomes: +442071838750

Cleansing mobile numbers to E.164 format

Remove special characters and spaces

1. Open your file in Microsoft Excel or Google Sheets
2. Duplicate your Mobile Number column and retitle the new column as Mobile2. This ensures you retain the original value for reference.
3. Select the Mobile2 column
4. Open Find and Replace tool (Ctrl-H on PC or CMD-SHIFT-H on Mac)
5. Populate the Find what field with one of the following characters:
   

• -
• +
• (
• )
• .
• space (add one space only)
• Any other non-numeric character in the column

6. Leave the Replace with field empty (including no spaces)

7. Select Replace All. This will remove the specified character from all cells in the selected column.

8. Return to step 5 and apply the next character until the column contains purely numerics

9. If some mobile numbers display a warning that the number is stored as text (see below), select one record with this warning and use CTRL-space to select the whole column. Now click on the error message and select Convert To Number.
This will remove any leading zeros.

Add Country Code to mobile numbers

All mobile numbers must include the + symbol and a country code. The following example is based on a file requiring only one type of country code, e.g. +61 (Australia). 

If the file includes mobile numbers from multiple countries, it may be easier to filter each country to manage this process.

1. Select the complete dataset from the first cell/first row to the last cell/last row
2. Apply a filter to the dataset
3. Create a new column next to the Mobile2 and call it Mobile3
4. Filter the records by Country (if required)
5. Sort on column Mobile2. This will allow any numbers that don’t belong in the county’s mobile number series to be visible.
6. For numbers without a country code:

• Assuming you are working in cell N2:
• Enter the formula in the Mobile3 cell: =”+61”&M2
• This will prefix the value in cell M2 with +61
  (Replace 61 with the correct IDD number for the country selected)
  
  
• 
  
• Copy your formula and paste to other cells in the column that require the + and country code
  

7. For numbers with a country code:

• Assuming you are working in cell N8:
• Enter the formula in the Mobile3 cell: =”+”&M8
  This will prefix the value in cell M8 with +
  
  
• Copy your formula and paste to other cells in the column that require the +

8. Repeat from Step 4 if you are filtering by country

9. Remember to save your work

10. The data in Mobile3 column will be correctly formatted as E.164 and suitable for import to Jomablue

Additional information

• Wikipedia has a comprehensive list of all country codes here.
• As a general rule, when the country code is applied, the leading 0 is dropped. So 0499 999 999 becomes +61 499 999 999.
• If you re-open a CSV file, Excel will interpret the + symbol as an operator and only display the number string. In this case, the + must be reapplied via formula or cell formatting before saving and closing.