| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to sum cells and ignore the #div/0! 's ? | Excel Worksheet Functions | |||
| Ignore Text in Cells Referenced in a formula | Excel Discussion (Misc queries) | |||
| How do I set up a drop down validation to ignore any blank cells? | Excel Discussion (Misc queries) | |||
| Trendline to ignore empty cells | Charts and Charting in Excel | |||
| Help adding text values | Excel Worksheet Functions | |||
Linear Mode
