Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delimited file format issue
i get reports from (idx) a program that are delimited files. the
columns are all fine but 1 and i can't figure it out. its supposed to be money and comes across as 11500. in the original program (idx) it would look like 115.00 and that is how i want it to look in excel. i tried the easy stuff like formatting and i keep getting 11500.00 or 1150.0 in my simple mind i thought it would be easy to move that decimal but its not turning out that way. i have thousands of line items so doing it one by one is not the optimum solution. any help is appreciated. excel 2003 os XP |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delimited file format issue
You really want to divide all these numbers by 100 - formatting won't
affect the values. Put the number 100 in an empty cell somewhere, then select that cell and click <copy. Then highlight all the cells (or columns) that you want to change and click on Edit | Paste Special | Values (check) | Divide (check) | OK then <Esc. You can then delete the 100 from the first cell. Hope this helps. Pete On Nov 6, 10:21 pm, evilyn wrote: i get reports from (idx) a program that are delimited files. the columns are all fine but 1 and i can't figure it out. its supposed to be money and comes across as 11500. in the original program (idx) it would look like 115.00 and that is how i want it to look in excel. i tried the easy stuff like formatting and i keep getting 11500.00 or 1150.0 in my simple mind i thought it would be easy to move that decimal but its not turning out that way. i have thousands of line items so doing it one by one is not the optimum solution. any help is appreciated. excel 2003 os XP |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
delimited file format issue
On Nov 6, 7:33 pm, Pete_UK wrote:
You really want to divide all these numbers by 100 - formatting won't affect the values. Put the number 100 in an empty cell somewhere, then select that cell and click <copy. Then highlight all the cells (or columns) that you want to change and click on Edit | Paste Special | Values (check) | Divide (check) | OK then <Esc. You can then delete the 100 from the first cell. Hope this helps. Pete Pete, Thanks sooooo much that's awesome and it worked like a charm. I'll add the step to the macro and voila! Thanks so much for your help. E |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel CSV file format issue... | Excel Discussion (Misc queries) | |||
Excel CSV file format issue... | Excel Discussion (Misc queries) | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) | |||
How do I save format changes in a comma delimited file? | Excel Worksheet Functions | |||
save excel file from a table delimited file (.txt) using macros | New Users to Excel |