Formula to extract number values only

[April]

Hi,
I can't seem to remember how to use a formula to extract numbers only. For
example, I would like to take a create a formula to pull only "3.5" for a
cell that containts "Sum: 3.5"...

 

[Luke M]

In your example, you could use:
=--(RIGHT(A2,3))

If your cells follow a pattern of:
Text(space)number

You can use:
=--(RIGHT(A2,LEN(A2)-FIND(" ",A2)))

 

[Mike H]

April,

you only give one example so it's a bit hit annd miss I'm afraid but how
about this

=LOOKUP(6.022*10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$100))))

all in one line of course

Mike

 

[T. Valko]

cell that containts "Sum: 3.5"...

If the number is *always* after the space:

A1 = Sum: 3.5

=--MID(A1,FIND(" ",A1),15)

 

[Ron Rosenfeld]

Hi,
I can't seem to remember how to use a formula to extract numbers only. For
example, I would like to take a create a formula to pull only "3.5" for a
cell that containts "Sum: 3.5"...

If the number is always at the end of the string, then:

=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0")),15)

--ron

 

[Rick Rothstein]

I can't seem to remember how to use a formula to extract numbers only. For

If the number is always at the end of the string, then:

=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0")),15)

I think that will fail if there are other numbers appearing earlier in the
string. Here is a method to pull the number at the end no matter what is in
front of it...

=LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99)))

 

[Ron Rosenfeld]

I think that will fail if there are other numbers appearing earlier in the
string.

If the number is always at the end of the string, then:

--ron

 

[Rick Rothstein]

Sorry, I misread what you meant by that... I thought you were referring to
the number that the OP was after, as per his example, being at the end of
the text no matter what what in front of it.

 

[Ron Rosenfeld]

Sorry, I misread what you meant by that... I thought you were referring to
the number that the OP was after, as per his example, being at the end of
the text no matter what what in front of it.

As we've both noted before, figuring out all the possible variants, given an
initial limited posting of examples, can be tough, and lead to initial
inappropriate responses.

For example, what if there could be multiple numbers, but only the last set of
numbers is wanted; but there could be text following.


--ron