Truncate / Substitute Formulas

[Jason W]

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.

 

[T. Valko]

FW345A-A

Are the numbers to extract *ALWAYS* together?

Will there ever be strings like this:

F5W345A-1
1-FW345A-A
FW34A5A-A

 

[JLatham]

See this page:
http://office.microsoft.com/en-au/excel/HA011549011033.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 lik
{=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.

 

[Rick Rothstein]

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?