Extract number from text cell

[Clare1805]

I have an extract from a call logging system; one of the columns is a description of the call (this column is extracted in Text format). Within some of the descriptions is a reference number which I need to extract (sadly this number isn't kept in its own field) - is there any way to determine whether a cell has numbers in it, and if so is there a way to pull them out into a seperate column? (preferably using a formula as I'm no good at VBA!)

Hope this makes sense and thanks in advance for your help!

 

[syar]

Hi Bary!

I have same situation, but my cell numbers are diffrent in length. Will you please suggest how can I change your formula to meet with my need.

 

[barry houdini]

Hi Bary!

I have same situation, but my cell numbers are diffrent in length. Will you please suggest how can I change your formula to meet with my need.

How long are the numbers, are they always the same length? In general you should change the two 6s to whatever length that is, and also the 5 too, so to search for an 8 digit number....

=LOOKUP(10^8,MID(SUBSTITUTE(SUBSTITUTE(A1," ","x"),".","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0)

but note it might not work if your numbers have leading zeroes.......

 

[syar]

Thank you for ur help. You solve my problem. Thank again

 

[sumi125]

its grt but i wna ask you why its taking only 6 character figure

 

[sumi125]

hello u genius barry
ignore my previous thread, can u evaluate the formula, i mean its very difficult to understand it. can you please mention how its work