Using Excel to format UK phone numbers ready for upload to FacebookPosted by in facebook advertising
Both Google AdWords and Facebook offer the opportunity to upload customer data to use for advert targeting. The platforms attempt to match your customer data to data it holds on its users to achieve an ‘audience’ available for use in targeting.
The most common data uploaded is email addresses which is self-explanatory, but Facebook offers another useful method – matching by phone number.
However, the phone number format Facebook requires is country code + area code + number
Key points are:
1. Phone numbers should include a country code
2. Phone numbers should no leading zeros
3. Phone numbers should not have spaces (digits only)
For UK users, data will most likely come from your company CRM system. Depending on the system you use you may well not be able to download your phone numbers in the required format for Facebook.
Use the following formula in your Microsoft Office Excel spreadsheet to turn your phone number into the required format for Facebook.
Cell A1 has the phone number, e.g. 01284 774870
In Cell B1 enter the following formula:
=IF(A1<>“”,SUBSTITUTE(CONCATENATE(“44″,RIGHT(A1, LEN(A1)-1)),” “,””),””)
Let’s break this formula down:
1. If there is no data in cell A1 you won’t get any output in B1
2. If there are any spaces in the phone number these will be removed
3. We will add “44” (UK country code) to the beginning of every number
4. We will strip off the first ‘0’ from the phone number
Therefore if A1 is 01284 744870, B1 will show 441284774870 – perfect for uploading to Facebook.
If you have a long list of phone numbers remember that double-clicking the bottom right corner of cell A2 (once it turns to a plus sign) will replicate the formula through all the cells in column B
This is not foolproof – it does require a consistent format of phone number from the file you downloaded from your CRM system, but hopefully this is of use.
Once you have column B I tend to copy these to a new sheet (remember to paste values) and save as a .csv file ready for upload to Facebook.