How to ignore the last char in some cells
 

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

How to ignore the last char in some cells
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mmcap" wrote:
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

How to ignore the last char in some cells
 

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mmcap" wrote:
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

How to ignore the last char in some cells
 

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



"mmcap" wrote:

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mmcap" wrote:
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

How to ignore the last char in some cells
 

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

"mmcap" wrote in message
...
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



"mmcap" wrote:

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mmcap" wrote:
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