I have a spreadsheet with two columns of data imported from an Access file. The numbers in the Access file are in this format: 1234.5 and 12345.6; I want them to be displayed as: 12 34.5 and 123 45.6 in the Excel spreadsheet.
Using the custom format option in “format cells” is not working. I have tried the following custom format: 00 00.0 and 000 00.0 and although it works fine for data entered directly into the spreadsheet, it won’t work for the imported data.
Does anyone have any ideas as to how I can fix this? I do not want to reformat the Access data directly.
Ok, in the time since I posted the question, I’ve figured it out for myself.
The Access file had the data columns formatted as “text”. This seemed to overide any formatting I tried to apply in Excel. By redefining the Access columns as “number”, and going through a frustrating trial and error process trying to figure out what it was doing with my decimal places, I fixed the problem.
If you ever have this problem in Excel and cannot acces the Access file, try multiplying the cell by 1 (i.e. in cell B1 “=A1*1”) then copy>paste special…as values those numbers and then applying the format you want. That little trick has worked on many a data set that were in some strange format…don’t know why.