Using Excel to format UK phone numbers ready for upload to Facebook
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 create an ‘audience’ available for use in targeting.
The most common data uploaded are email addresses which is self-explanatory, but Facebook offers another useful method – matching by phone number.
However, to enable phone numbers to matched to the information Facebook holds about its users, the uploaded file requires a specific format so the data can be matched.
Facebook Phone Number Formatting
The phone number format Facebook requires is country code + area code + number
Key points are:
- Phone numbers should include a country code
- Phone numbers should no leading zeros
- 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 original phone number from your CRM system, 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:
- If there is no data in cell A1 you won’t get any output in B1
- If there are any spaces in the phone number these will be removed
- We will add “44” (UK country code) to the beginning of every number
- We will strip off the first ‘0’ from the phone number
Therefore if A1 (your original cell) is 01284 744870, B1 (your formatted cell) 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 B2 (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.
You can use your csv file to upload a custom audience in Facebook
My developer is trying to convince me to move to .net from PHP.
I have always disliked the idea because of the expenses.
But he’s tryiong none the less. I’ve been using Movable-type
on several websites for about a year and am concerned about switching to
another platform. I have heard great things about blogengine.net.
Is there a way I can import all my wordpress content into it?
Any kind of help would be greatly appreciated!
This marketing news is really very useful…. Keep instantly updating these types of informative blogs……..