Extract & Copy a phone number from text

[alpha.martinez]

I have hundreds of rows in excel with lots of numbers in between, the only pattern there is that alsmost all cells have a 10 digit number. Is it possible to write some vba that will extract and paste 10 digit number on the cell next to it?


These would be some samples;

The number found on this customer's account is 8055555555 along with the account #...
The VM number on account 123465 was changed recently to 8661234567 due to an order error…
At 0800 we received another call from 2523691234 regarding account 123456.

There are multiple numbers in the text, on each row, but I need to only extract and copy to next cell the ones that are 10 digits.

Thanks!

[NBVC]

Please give some sample data that covers all possibilities.

[Marcol]

Try this

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

This will return 10 characters beginning with the first number in the string, it should be okay provided your strings contain exactly 10 consecutive numbers (blanks included).

Hope this helps

[alpha.martinez]

The problem here is that we have multiple numbers within same string of text. Some are account numbers that are only 6 digits and some are other numbers, the 10 digit number I want to extract is not always the first number to the LEFT, RIGHT or MID, it is in random order.

The formula finds some the number I need only when it is the first set of numbers in the text string.

[Marcol]

As NVBC says post a sample workbook showing the range of data you are trying to handle.

The formula given was just a shot in the dark!

[NBVC]

If you install a free addin from here: Morefunc.

Then you can apply formula:

=REGEX.MID(A2,"\d{10}")

copied down.

Seems to work on the samples you provided.

[alpha.martinez]

The reason why I provided only the samples above is due to the data I'm working is got a high level of privacy due to it reveals customer's information from the company I work for.

I have replicated the data and uploaded a sample that resembles the data I am working with.

Thank you!

[NBVC]

I just tested my formula with the addin in your attachment.. and it worked for every one of them.

[alpha.martinez]

It did work for all thank you!, Would you be able to also provide a VBA solution for this, I'd like to apply this without having to install something on all computers. We have multiple users who will need some type of macro that will enable them to do this on their computer.

[NBVC]

That addin is supposed to have a feature that allows you to embed it in the wrokbork for sharing... It wasn't working at one point, but not sure if it is fixed now....

But unfortunately not I for the VBA version, that is not my expertise... I know there are others here who know how to do the regex functions with VBA... one will hopefully come by (or produce an alternative VBA function).

[NBVC]

Ok, I just stole this from here: http://www.ozgrid.com/forum/showthre...t=37624&page=1 with credit to Richie(UK)


and adjusted it... and it seemed to work:

Please Login or Register  to view this content.

Then Apply function =RE10(A2) and copy down

[anurag00781]

Ok, I just stole this from here: http://www.ozgrid.com/forum/showthre...t=37624&page=1 with credit to Richie(UK)


and adjusted it... and it seemed to work:

Please Login or Register  to view this content.

Then Apply function =RE10(A2) and copy down

Hello there,

Many thanks for your help.
But I m stuck here, I used ur function as described by u and it worked fine for the first time, but when I exited the Excel 2007 and again opened the file and tried to extract the numbers from text, it didn't work. Then I again created a new module in Excel VBA and the tried to execute it. But this time it showed no result for cellphone numbers, instead it showed "#REF!"
Could you please help me in getting this function again working for me?
I am attaching the excel file.

Many many thanks in advance.

[arlu1201]

Anurag,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.