Extracting numbers, with a twist...!

[Legacy 171839]

Hi all,

So, I'm looking to extract phone numbers from a long list of company and number in the same cell.

I have various formuals to extract numbers from cells, but none are working, and here's why:

Firstly, all the formulas I try ignore the '0' at the start of the number

Example: Cell contains "Company A 0800123456" - formula returns "800123456".

Secondly, the format in which the company name/number has been entered is not always the same - ie, sometimes the number has spaces. That gives me the follwoing problem:

Example 2: Cell contains "Company B 0800 123 456" - formula returns "800"
Cell contains "Company C 08123 456654 " - formula returns "8123"

So basically, I'm looking for help to extract the full phone number from the cell, regardles of how its been inputted.

Thanks in advance for your help guys, you haven't let me down yet.

Frierpie

 

[T. Valko]

Hi all,

So, I'm looking to extract phone numbers from a long list of company and number in the same cell.

I have various formuals to extract numbers from cells, but none are working, and here's why:

Firstly, all the formulas I try ignore the '0' at the start of the number

Example: Cell contains "Company A 0800123456" - formula returns "800123456".

Secondly, the format in which the company name/number has been entered is not always the same - ie, sometimes the number has spaces. That gives me the follwoing problem:

Example 2: Cell contains "Company B 0800 123 456" - formula returns "800"
Cell contains "Company C 08123 456654 " - formula returns "8123"

So basically, I'm looking for help to extract the full phone number from the cell, regardles of how its been inputted.

Thanks in advance for your help guys, you haven't let me down yet.

Frierpie

Try this...

Book1
*	A	B
2	Company A 0800123456	0800123456
3	Company B 0800 123 456	0800 123 456
4	Company C 08123 456654	08123 456654
Sheet1

This formula entered in B2 and copied down:

=MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),20)

 

[Legacy 171839]

Biff - you are some kind of legend.

Thanks much and all the best

Frierpie

 

[jessica_ful]

I would run VBA on the cells to extract the numbers and store the values in an adjacent cell. This would work if the Company names don't have numbers.

Dim stri, striIndex, letterj

for i=1 to numberofdataentries

n=get the length of the string for the cell​

stri = cells(i,1)​

'loop through the elements inside each cell
for j=1 to n​

striIndex = Left(stri, j)​

letterj= Right(striIndex, 1)​

is letterj a number?​

if so, stri= stri+letterj​

Next j​

cells(i,2) = stri​

next i

Hopefully this approach isn't too novice. Good luck.

Jessica

 

[T. Valko]

Biff - you are some kind of legend.

Thanks much and all the best

Frierpie

You're welcome. Thanks for the feedback!