Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove value after decimal point
H
I have a workbook with six worksheets. The worksheets contain client information, including a column called Empl_ID. This is a unique field that allows us to select information from each worksheet about specific employees. However, the six sheets which were sent to us have varying cell formats. One sheet will show a client Empl_ID as being 11024 and another will show the client Empl_ID as being 11024.2385456 Needless to say, this produces incorrect information. Can someone give me a formula to remove the decimal point and any numbers that were to the right of the decimal point. I will have to run this formula 6 times - once in each worksheet The numbers must be completey removed so the value of the cell is the number without any decimal value. This is the only way I will be able to merge data from the six worksheets for specific clients without getting incorrect data Thanks in advanc Malcolm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove value after decimal point
Hi Malcolm
try TRUNC. e.g. TRUNC(123.456) will return 123 HTH Frank Malcolm wrote: Hi I have a workbook with six worksheets. The worksheets contain client information, including a column called Empl_ID. This is a unique field that allows us to select information from each worksheet about specific employees. However, the six sheets which were sent to us have varying cell formats. One sheet will show a client Empl_ID as being 11024 and another will show the client Empl_ID as being 11024.23854562 Needless to say, this produces incorrect information. Can someone give me a formula to remove the decimal point and any numbers that were to the right of the decimal point. I will have to run this formula 6 times - once in each worksheet. The numbers must be completey removed so the value of the cell is the number without any decimal value. This is the only way I will be able to merge data from the six worksheets for specific clients without getting incorrect data. Thanks in advance Malcolm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove value after decimal point
Hi Malcolm,
A quick & dirty way would be to insert a blank column to the right of the offending once, then use Data|Text to Columns|Delimited and chose 'other' with the decimal point as the delimiter. This will put the decimal values in the right-hand column, which can then be deleted. Bear in mind though, that your values will be rounded down to the nearest whole number, rather than being rounded to the nearest whole number. Cheers "Malcolm" wrote in message ... Hi I have a workbook with six worksheets. The worksheets contain client information, including a column called Empl_ID. This is a unique field that allows us to select information from each worksheet about specific employees. However, the six sheets which were sent to us have varying cell formats. One sheet will show a client Empl_ID as being 11024 and another will show the client Empl_ID as being 11024.23854562 Needless to say, this produces incorrect information. Can someone give me a formula to remove the decimal point and any numbers that were to the right of the decimal point. I will have to run this formula 6 times - once in each worksheet. The numbers must be completey removed so the value of the cell is the number without any decimal value. This is the only way I will be able to merge data from the six worksheets for specific clients without getting incorrect data. Thanks in advance Malcolm --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.561 / Virus Database: 353 - Release Date: 13/01/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove value after decimal point
for each cell in selection
if isnumeric(cell.Value) then cell.Value = int(cell.Value) end if Next -- Regards, Tom Ogilvy "Malcolm" wrote in message ... Hi I have a workbook with six worksheets. The worksheets contain client information, including a column called Empl_ID. This is a unique field that allows us to select information from each worksheet about specific employees. However, the six sheets which were sent to us have varying cell formats. One sheet will show a client Empl_ID as being 11024 and another will show the client Empl_ID as being 11024.23854562 Needless to say, this produces incorrect information. Can someone give me a formula to remove the decimal point and any numbers that were to the right of the decimal point. I will have to run this formula 6 times - once in each worksheet. The numbers must be completey removed so the value of the cell is the number without any decimal value. This is the only way I will be able to merge data from the six worksheets for specific clients without getting incorrect data. Thanks in advance Malcolm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make value whole (remove decimal point) | Excel Worksheet Functions | |||
remove decimal point for csv sheet | Excel Discussion (Misc queries) | |||
in Excel 2007, how do you remove the decimal point in General | New Users to Excel | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |