Yet another excel question by Dog80!
I have a column with numbers, eg. 385.5444. Using custom formats I managed to convert this number to A000385 (got rid of decimals and added A000). The problem is that the cells are still numbers underneath. So, whenever I try to import the data to Access, I get 385.5444 instead of A000385.
Is there any way in Excel to get rid of the original values and keep the A000xxx as text (so it won’t give me problems during import)?
My question is, how did you get 385.5444 to turn into A000385, I always get A000386 when trying to get you an answer.
However you do it, use =TEXT(A1,“A000000”) where A1 is the cell you have the data and A000000 is the custom format code. I used A000000 to get A000386. I Suppose I could have done =TEXT(FLOOR(A1,1),“A000000”), that would have gotten A000385.
A little more thought here.
What I said will give you text, but it would also create a new cell (or whole column if you copied down). The quick way around this is, copy the spreadsheet, open a new sheet, paste special “values”, cut the column with the text and paste it in the column with the numbers. This should give you the proper format for importing into access.
Thank you Jimpy! It worked. And as you correctly stated, the value in my example should be 386, not 385. My bad!
Something odd: In my computer the =TEXT(A1,“A000000”) didn’t work. I had to substitute the comma with a semicolon.
You’re welcome. The comma/semicolon thing may be a regional settings thing. But at least you got it working.