Pau Lo
asked on
number formatting issues
I received a report from an Oracle Database (SQL Developer), one of the columns (which was supposedly a currency value, was formatted with a number of leading spaces and was recognised in excel as "general". In the next column I did =trim(B2) to cut out the leading spaces, and then I just copied and pasted values and number formats, which gave me the values with no leading or trailing spaces. I then highlighted the entire column and tried to format as "currency", this did that but then created an error against every cell in that column. I tried to import this data into access, and selected that column as currency, but I get an import error for practically every cell. How can I get excel (and then ultimately access) to recognise these cells as true currency values, so next time I import to access it imports them fine?
ASKER
as per my post I have already done that
ASKER
If I right click column B (which is the trim representative of the currency values), it recognises them now as currency, but next to each cell is an error "number stored as text", or the 2nd option is "convert to number". I dont get it, why does the error state stored as text when if you right click it it is actually in currency format (which is what I want...)
Hi
If it is not confidential can you attach that file?
If possible just go thru the below link and see if you can sort it out
https://support.office.com/en-us/article/Format-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4
If it is not confidential can you attach that file?
If possible just go thru the below link and see if you can sort it out
https://support.office.com/en-us/article/Format-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4
ASKER
I've done both the above steps (trim to get rid of the spaces, then format as currency), but excel still doesnt like it, and thinks they are text, nor does access, when trying to import. I even right click the cell and its formatted currency. but then the error says "number stored as text", and the 2nd option is "convert to number", which I dont want to do, I want it to remain as currency!! If I do convert the number the error goes away but I cant do that for 2000 cells!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you could try the above steps with the help of this small macro....
Sub ConvertToNumbers()
Range("B2", Range("B1").End(xlDown)).Copy
Range("B2", Range("B1").End(xlDown)).PasteSpecial Paste:=xlPasteAll, operation:=xlAdd
Application.CutCopyMode = 0
End SubASKER
>5) Now under Operation, select Add and click on OK.
that worked, thanks..
that worked, thanks..
You're welcome pma! Glad it worked for you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you should either let that person who sent it to remove the spaces or you can
Insert a new column after B say in the C column put =Trim(B1) and then copy that formula till end-of-file and delete column B.
If this is a daily exercise then write a macro using a command button in an excel file
To read your daily file
Then add a new column C
Trim it as =Trim(B1)
Copy it till EOF
Delect Colum B
so that you can get the file in the format you want.