How do I remove text from a number?

S

Smyth

How can I remove the "M" from the number 12,000M? The functions left & right
don't work becuase the number of digits before the M can change however M
will always be last.
 
A

Andy B.

You can try doing a search and replace on the word M

Search and replace M with nothing - that should get rid of the M in th
cells

Good luck

Andy B
 
M

Mike Lewis

Here is one way, it will turn it back into a real number:

=VALUE(REPLACE(D9,LEN(D9),1,""))

where D9 is the text to change

Good Luck

Mike
 
I

icestationzbra

did you try the MID function? if M is always the last character:

=MID(A1,1,(LEN(A1)-1))

alternately, this formula will give you all the character upto M, an
if there are characters beyond M, they will be left out:

=MID(A1,1,(SEARCH("M",A1,1)-1)
 
R

Ron Rosenfeld

How can I remove the "M" from the number 12,000M? The functions left & right
don't work becuase the number of digits before the M can change however M
will always be last.
Click to expand...

Is the last character always an "M"?

If so, then

=SUBSTITUTE(A1,"M","")

If the last character can be anything:

=LEFT(A1,LEN(A1)-1)


--ron
 
Top