Uploading your contacts into our online messaging platform Message Box, couldn’t be easier. You simply add your customers’ mobile numbers into an Excel file, save it, and use our online platform to upload all your numbers in bulk, it really is that simple – to see how easy it is, read our blog on how to upload your contacts here.
But what happens if your CRM doesn’t allow you to export just the mobile numbers? What if when you download all your customers’ data, it includes your customers’ name, address, and mobile number all in the same cell? This can be a major pain, but there are 2 ways you can extract your customers’ mobile numbers into a single cell.
Option 1 – Manually copy and paste each individual mobile number into a new cell, which if you have 10 won’t take long, but if you have 10,000 it may take a while.
Option 2 – Simply read this post, use a very simple formula and complete this task in a matter of minutes, and in the process become an Excel wizard.
If option 2 was your pick, all you need is one very simple formula . . . =MID(A1,FIND(“07”,A1,1),11)
To some, the formula above looks like another language, but it isn’t complicated and the best part, you don’t even need to know what it does or how it works to extract your mobile numbers. If your mobile numbers don’t include any spaces, simply jump the section ‘Using the formula in practice’ and follow the 4 simple steps to extract your data.
Numbers with spaces in
Before you start, make sure your mobile numbers have no spaces in, if they do you will need to remove these, but again don’t worry we have a simple solution for you.
Highlight the column with all your data in and click Ctrl+F or your equivalent find feature on your computer. A small ‘Find and Replace’ pop up box will appear, from here navigate to the ‘Replace’ tab and in the box that says ‘Find what:’ press your space key. Make sure in the ‘Replace with:’ there is nothing, hit the backspace a couple of times to ensure there is nothing in that box. Now press the ‘Replace All’ button.
This will find all spaces in the column you have highlighted and replace them with nothing – essentially removing any spaces.
Using the formula in practice
To extract mobile numbers that are contained within a string of text, follow the 4 simple steps below.
1. Download your customers’ data and paste this into the first column in your spreadsheet. Make sure your data starts in cell A1, there is no need for a column header.
2. Copy this formula =MID(A1,FIND(“07”,A1,1),11) and paste this into B1. Hit enter and your customers’ mobile number will now be displayed in this cell.
3. Copy this formula all the way down to the bottom of your data.
4. Highlight all your customers’ mobile numbers, now in column B, and copy them. Then, open a new Excel spreadsheet, and in cell A1 on your new spreadsheet, paste your mobile number – make sure you paste as ‘Values (V)’ in the ‘Paste Special.’ header.
Now you have your mobile numbers in a new Excel spreadsheet you can upload it these into your Message Box account. Make sure before uploading your contacts that you format the cells as a number, to see how you can do this, read our blog.
*Please note – If your mobile numbers are not a UK mobile number, ie they are longer or shorter than 11 digits, or if the proceed with 44, you will have to change the “07” and 11 in the formula.
Changing the “07” – If your numbers look like this, 447569875456 the “07” will need to be “447” – the first few digits of your mobile numbers including the UK international dialling code.
Changing the 11 – If your numbers look like this, 447569875456 then the 11 at the end of the formula will need to change to 12 – the number of digits in your mobile number.
The formula explained in detail
For those who want to expand their Excel knowledge, below we have explained the formula and how it works in more detail below.
There are 2 parts to the formula, the first part we will call the MID formula and the second part the FIND formula. The MID formula returns the mobile number and the FIND formula finds the mobile number. Even though the MID formula comes first, the FIND formula is the first things that the whole formula does, it finds the mobile number, and then once it has found it, returns it and displays it in that cell.
The MID formula: =MID(A1,9,11)
The MID formula returns the mobile number and displays this in a cell. We have broken down the formula into the different areas and explained them in more detail below.
A1 – The cell that your mobile numbers are in – this will change as you copy the formula down.
9 – This is where the first digit of where your mobile starts – so this number would essentially change for different cells as your mobile number wouldn’t always start from the 9th space in (this is the main reason we need the FIND formula, to change this automatically depending on where your mobile number starts in the cell)
11 – The number of digits in a UK mobile number
The FIND formula: =FIND(“07”,A1,1)
The FIND formula finds how many characters in your mobile number begins, for example, if the data in your cell looks like this; TedSmith07898767678 – then the FIND formula will bring back the number 9 as this is how far in your mobile number starts.
Again, we have broken down the formula and explain what each section does.
“07” – The first two digits of your customers’ mobile number. If your number includes the UK international dialling code, then you would start with “447”.
A1 – The cell that your mobile numbers are in – this will change as you copy the formula down.
1 – Finds how many characters in your mobile number starts – this finds “07”.
The whole formula: =MID(A1,FIND(“07”,A1,1),11)
The whole formula brings both of those 2 previous formulas together. The MID formula is at the beginning as this is what we ultimately want to do, we want to physically show the mobile number. We then use the FIND formula in to find the mobile number.
A1 – The cell that your mobile numbers are in.
“07” – The first two digits of your customers’ mobile number.
1 – Finds where in the cell the first digit of your mobile number starts.
11 – The number of digits in a UK mobile number.
