How to ignore the last char in some cells

[Guest]

I need to be able to ignore the last char in a cell that contains a number
and a letter suffix like 100K, 30B, 70M, 10% (thousand, billion, million
and percent).
I have to copy and paste a lot of numbers from another sorce and they
include the suffixes with the number. Is there a way to do this?
Thanks

 

[Guest]

Assuming the source data is pasted in A1 down

Put in B1:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)+0))
Copy down

 

[Guest]

Works great for pos. numbers but not for neg. numbers. It thinks I'm trying
to write a formula.

 

[Guest]

I take that back it does work on neg. numbers that are already pasted. You
just can't type in a neg. number.
Much Thanks
Norm

 

[T. Valko]

When typing in entries like -100M, you have to either preformat the cell as
TEXT (which might not be desirable) or precede the entry with an apostrophe:
'-100M. The apostrophe will not show up in the cell but will show up in the
formula bar. This just tells Excel that what you are entering is text and
Excel should "butt out" and not be so $%^&** helpful!

Biff