Truncate / Substitute Formulas
 

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.

Truncate / Substitute Formulas
 

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.

Truncate / Substitute Formulas
 

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.

Truncate / Substitute Formulas
 

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.