Is there a formula that removes everything except numbers from a cell? For
example, if a cell contained the text FW345A-A the formula would return the
value 345. I got it to somewhat work using SUBSTITUTE on top of itself, but
there seems to be a limit in the number of characters you can add to the
string. It won't let you add all 26 letters and the dash.

FW345A-A

Are the numbers to extract *ALWAYS* together?

Will there ever be strings like this:

F5W345A-1
1-FW345A-A
FW34A5A-A

--
Biff
Microsoft Excel MVP


"Jason W" <Jason wrote in message
...
Is there a formula that removes everything except numbers from a cell?
For
example, if a cell contained the text FW345A-A the formula would return
the
value 345. I got it to somewhat work using SUBSTITUTE on top of itself,
but
there seems to be a limit in the number of characters you can add to the
string. It won't let you add all 26 letters and the dash.

See this page:
http://office.microsoft.com/en-au/ex...549011033.aspx
At the end of the article you'll have this formula (which would be in a cell
like B1 or C1 and refers to a value such as FW345A-A in cell A1):
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

Note that it is an array formula, so you must 'commit' it with
[Shift]+[Ctrl]+[Enter] so that it ends up looking like
{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9) ,1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))}
in the formula bar. You can now fill that formula down the sheet as far as
you need to take it.

So, thanks to Ashish Mathur, there is a solution.

"Jason W" wrote:

Is there a formula that removes everything except numbers from a cell? For
example, if a cell contained the text FW345A-A the formula would return the
value 345. I got it to somewhat work using SUBSTITUTE on top of itself, but
there seems to be a limit in the number of characters you can add to the
string. It won't let you add all 26 letters and the dash.

You need to tell us more about your values. For example... Are the numbers
always 3 digits long? Does the number always start at position 3 in the
text? Will there ever be other numbers in the text (such as FW345A-A67B)
and, if so, what do you want to do with them? Anything else you can think of
that will describe what you have and what you want to get from it?

--
Rick (MVP - Excel)


"Jason W" <Jason wrote in message
...
Is there a formula that removes everything except numbers from a cell?
For
example, if a cell contained the text FW345A-A the formula would return
the
value 345. I got it to somewhat work using SUBSTITUTE on top of itself,
but
there seems to be a limit in the number of characters you can add to the
string. It won't let you add all 26 letters and the dash.