Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting
I am sure I am doing something incredibly stupid here but just cant seem to
figure this out. I have a budget work sheet. The first column is the account number. the next twelve columns are each month's budget (Jan-Dec). In the original budget file, some numbers were simply keyed in and others are formulas. (Some are a gross # times a percentage and others a gross # divided by 12 months). I am creating a text file to import the budget data into my financial system. The first thing I did was to take the budget file and copy it into its own file by using paste special - values and formats. my number formats are all ##### with no decimals. This worked perfectly for those #s that were hard keyed in. The ones that are from formulas - while visually they look like they are formatted #####, if you are sitting on the cell, it actually is formatted #####.#########. I need to concatenate all the columns into one cell with a comma separating each value. The numbers cannot contain any decimals but I cannot seem to get rid of them. I have tried everything. Can someone help? thanks.! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting
A format is just a view of a number. While the actual number may have
decimals the format does not have to show all of the decimals. That is the case here. Your actual number has decimals. You can use the round function to remove the zeros =round(A1, 2) will remove the zeros from the number stored in cell A1. -- HTH... Jim Thomlinson "Mary Lou" wrote: I am sure I am doing something incredibly stupid here but just cant seem to figure this out. I have a budget work sheet. The first column is the account number. the next twelve columns are each month's budget (Jan-Dec). In the original budget file, some numbers were simply keyed in and others are formulas. (Some are a gross # times a percentage and others a gross # divided by 12 months). I am creating a text file to import the budget data into my financial system. The first thing I did was to take the budget file and copy it into its own file by using paste special - values and formats. my number formats are all ##### with no decimals. This worked perfectly for those #s that were hard keyed in. The ones that are from formulas - while visually they look like they are formatted #####, if you are sitting on the cell, it actually is formatted #####.#########. I need to concatenate all the columns into one cell with a comma separating each value. The numbers cannot contain any decimals but I cannot seem to get rid of them. I have tried everything. Can someone help? thanks.! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting
Thank you soooo much. you have no idea how long i struggled with this.
"Jim Thomlinson" wrote: A format is just a view of a number. While the actual number may have decimals the format does not have to show all of the decimals. That is the case here. Your actual number has decimals. You can use the round function to remove the zeros =round(A1, 2) will remove the zeros from the number stored in cell A1. -- HTH... Jim Thomlinson "Mary Lou" wrote: I am sure I am doing something incredibly stupid here but just cant seem to figure this out. I have a budget work sheet. The first column is the account number. the next twelve columns are each month's budget (Jan-Dec). In the original budget file, some numbers were simply keyed in and others are formulas. (Some are a gross # times a percentage and others a gross # divided by 12 months). I am creating a text file to import the budget data into my financial system. The first thing I did was to take the budget file and copy it into its own file by using paste special - values and formats. my number formats are all ##### with no decimals. This worked perfectly for those #s that were hard keyed in. The ones that are from formulas - while visually they look like they are formatted #####, if you are sitting on the cell, it actually is formatted #####.#########. I need to concatenate all the columns into one cell with a comma separating each value. The numbers cannot contain any decimals but I cannot seem to get rid of them. I have tried everything. Can someone help? thanks.! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions |