Formula to remove an "R" and convert cell to number

**maymano** · 06-05-2011, 05:33 AM

I have a column that has R 154.10. I want to remove the "R" and convery the 154.10 to a number. Thanks.

**royUK** · 06-05-2011, 05:42 AM

Use a helper column, i.e. have an empty column next to the cells. Assuming Column A, in B1 & copy down

=TRIM(RIGHT(A1,LEN(A1)-2))

**Roger Govier** · 06-05-2011, 06:39 AM

a quick "heads up" Roy.
The OP wanted a number as the result so maybe

=--TRIM(RIGHT(A1,LEN(A1)-2))
to force the conversion from text to numeric

**zbor** · 06-05-2011, 06:44 AM

To extract last number in a word:

=LOOKUP(99^99, --RIGHT(A1, ROW($A$1:$A$10)))

**daddylonglegs** · 06-05-2011, 07:32 AM

Assuming the cell always has "R" then a space then a number....

=MID(A1,3,9)+0

**martindwilson** · 06-05-2011, 07:48 AM

and another one
=SUBSTITUTE(A1,"R","")+0

**Sadath31** · 06-05-2011, 09:43 AM

Why dont use 'Find & Replace'
Select the Range
Find- R
Replace (Blank)

**maymano** · 06-05-2011, 01:58 PM

Thanks all for the help!

Re: Formula to remove an "R" and convert cell to number