Ignore text Function

[Grant]

Is there function that will look at the value of a cell and ignore the text
but return the numeric value only.

Example: cell value is CH123XX
Retrun value : 123

Amount of text would be dynamic.

Thnanks

 

[Dave R.]

see the 2nd formula in Peo's post here;

http://groups-beta.google.com/group...t.functions/msg/646089eb944437e2?dmode=source

 

[Guest]

Hi Grant
One way, although it is a bit long winded, is to use Data/Text to Columns.
and then delete the columns you don't need.
You could then concatenate the columns that are left, back to one cell.

Using =LEFT() and =MID() functions will also do the trick, but if the length
of the string is dynamic, it may cause some serious errors.

HTH
Michael

 

[Jason Morin]

If the numbers are always together in the string, you
could use this formula (array-entered)

=MID(A1,MATCH(TRUE,bre,0),MATCH(TRUE,bre)-MATCH
(TRUE,bre,0)+1)

where bre is a defined name for:

=ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

If not, Gord Dibben posted a robutst macro solution:

http://tinyurl.com/5p5ms

HTH
Jason
Atlanta, GA