How to get a phone number from a text cell in Excel

How to get a phone number from a text cell in Excel

Adding contacts to your online messaging platform Message Box is simple. Simply add your customers’ mobile numbers to an Excel file and save it.

If your CRM doesn’t let you export just mobile numbers, what do you do? What if you could download your customers’ data with their name, address, and phone number all in one cell? There are two ways to extract your customers’ mobile numbers into a single cell.

Option 1 – Manually copy and paste each individual mobile number into a new cell.

Option 2 – Read this post, use a simple formula, and complete this task in minutes, becoming an Excel wizard.

Assuming you chose option 2, you only need one simple formula

=MID(A1,FIND(“07”,A1,1),11)

The formula above may appear complex to some, but it is not, and you do not need to understand it to extract your mobile numbers. Your data will be extracted in 4 simple steps if your mobile numbers do not contain spaces.

Numbers with spaces in

If your mobile numbers contain spaces, you will need to remove them before continuing, but don’t worry, we have a simple solution for you.

Select the data column and use Ctrl+F or your computer’s equivalent find feature. Then select the ‘Replace’ tab and in the ‘Find what:’ box, press the space key. Make sure there is nothing in the ‘Replace with:’ box by pressing backspace a few times. Now click ‘Replace All’.

This will find all spaces in the highlighted column and replace them with nothing, effectively removing them.

Make Use of the formula in practice

Follow the 4 steps below to extract mobile numbers from a text string.

  1. Install your spreadsheet and copy your customers’ data. No need for a column header if your data starts in cell A1.
  2. Paste =MID(A1,FIND(“07”,A1,1),11) into B1. Press enter to display your customers’ mobile numbers in this cell.
  3. Copy this formula to the end of your data.
  4. Copy all of your customers’ mobile numbers in column B. Then, in cell A1 of your new Excel spreadsheet, paste your mobile number as ‘Values (V)’ in the ‘Paste Special.’ header.

You can now upload your mobile numbers into your Message Box account. Before uploading your contacts, format the cells as numbers as described in our blog.

You must change the “07” and 11 in the formula if your mobile numbers are not UK mobile numbers, ie longer or shorter than 11 digits, or if you proceed with 44.

With numbers like 447569875456, the “07” must be changed to “447”.

For numbers like 447569875456, the 11 at the end of the formula needs to be changed to 12.

The formula in detail

For those who want to learn more about Excel, we have detailed the formula and how it works.

The formula has two parts: the MID formula and the FIND formula. It returns the MID number and finds the FIND number. Even though the MID formula comes first, the FIND formula finds the mobile number, returns it, and displays it in that cell.

The whole formula: =MID(A1,FIND(“07”,A1,1),11)

The whole formula combines the two previous formulas. The MID formula is at the start because this is what we want to do, show the mobile number. The FIND formula is used to find the mobile number.

A1 – Your mobile numbers’ cell.

“07” – Your customers’ mobile number.

1 – Finds the first digit of your mobile number in the cell.

11 – UK mobile number digits.

The FIND formula: =FIND(“07”,A1,1)

The FIND formula returns the number 9 if the data in your cell is as follows:

TedSmith07898767678.

Again, we’ve broken the formula down and explained each section.

“07” – Your customers’ mobile number. The international dialling code for the UK is 447.

As you copy the formula down, A1 will change to contain your mobile numbers.

1 – Finds the first character of your mobile number – “07”.

The MID formula: =MID(A1,9,11)

The MID formula displays the mobile number in a cell. We’ve divided the formula into sections and explained each one in more detail below.

As you copy the formula down, A1 will change to contain your mobile numbers.

Your mobile number does not always start from the 9th space in your cell, so this number changes for different cells (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 – UK mobile number digits

Leave a Reply