# How do I extract numbers from cells containing text and numbers?

In Excel I have many cells that include text and numbers in different parts of the cell.
How do I "extract" just the numbers in these cells and put them in a new column.
The number are in different parts of the cells so I can't use LEFT, MID, or RIGHT.
Extract-numbers-from-Lease-cells.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Software EngineerCommented:
This should do:
=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(\$1:\$25),1))* ROW(\$1:\$25),0),ROW(\$1:\$25))+1,1)*10^ROW(\$1:\$25)/10)

just starts of A2 you can change that.
ConsultantCommented:
Hi Michael,

You could use this in B2, and copied down:

{=LEFT(MID(A2,MATCH(TRUE,ISNUMBER(VALUE(MID(A2,ROW(\$1:\$9999 ),1))),0),999),MATCH(FALSE,ISNUMBER(VALUE(MID(MID(A2,MATCH(TRUE,ISNUMBER(VALUE(MID(A2,ROW(\$1:\$9999 ),1))),0),999),ROW(\$1:\$9999 ),1))),0)-1)}

It is an array formula, so enter it without the braces, using Shift-Ctrl-Enter.

See attached file.

Alan.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks Alan
Excellent solution

Michael
Author Commented:
Hi Martin

The other solution didn't work because he used MID which, because the numbers were in different parts of the cells, didn't pick the full number.

What do I do?

Thanks
Michael
Author Commented:
Thanks Alan
Excellent solution

Michael