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?
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi

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.
0
pma111Author Commented:
as per my post I have already done that
0
pma111Author Commented:
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...)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kanti PrasadCommented:
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
0
pma111Author Commented:
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!
0
ProfessorJimJamCommented:
pma11

if your data is in column A. then run the below macro and try and let me know.


Sub macro()
    Range("A:A" & rand("a" & Rows.Count).End(xlUp).Row).Select
    With Selection
        Selection.NumberFormat = "$#,##0.00"
        .Value = .Trim(Value)
        
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please follow these steps to see if this helps..

1) Input 0 (zero) in any empty cell.
2) Select the cell with 0 and press Ctrl+C to copy it.
3) Now select your Cells with values which appears as text but look like numbers.
4) Right click on one of the selected cell --> Choose Paste Special
5) Now under Operation, select Add and click on OK.
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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 Sub

Open in new window

0
pma111Author Commented:
>5) Now under Operation, select Add and click on OK.

that worked, thanks..
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome pma! Glad it worked for you.
0
Rob HensonFinance AnalystCommented:
looks like you have this resolved but thought I would add some reasoning why it was going wrong in the first place.

Although you have the Format set to currency that will only display as a currency if the cell contents are actually a number. Changing the format will only change how the cell appears, it won't actually change the contents. The TRIM function has created a text string that looks like a number.

Edit the TRIM function in one of these ways:

=TRIM(A1)*1

or

=VALUE(TRIM(A1))

the *1 will force Excel to re-evaluate the contents and will convert to number

VALUE does the same.

You will then be able to Copy & Paste as values correctly.

Thanks
Rob H
0
PatHartmanCommented:
Unless this is just a one-time import, in which case it doesn't much matter how you coerce the data, you should probably be solving the problem in Access rather than Excel.

I almost never import external data directly into a table.  Instead, I link to the spreadsheet/text file and then use a query to append the data to my table.  In the query, I get the opportunity to clean up data issues like this one.  To solve this particular problem, my append query would use the Val() function to pull out the numeric part of the string.  If the Val() function won't do it for you, then you could write your own function and use that.  The point is that going through Excel simply adds manual intervention which you probably don't want.

Currency in Access is both a data type and a format and the two should not be confused.  The Currency data type is simply a scaled integer with a fixed 4 decimal digits(12344500 logically equals 1234.45).  How you format this data is a separate issue.  Internally currency is an integer that is scaled during numeric operations (this is very helpful when you need to avoid floating point errors and so can't use single or double).  Formatting as currency adds the currency sign, commas, and decimal points.  Those characters make the field a text string.  Once formatted, a number is no longer a number so NEVER format in queries (unless you are specifically exporting to Excel and need to control how Excel will see the data).  Formatting is what we do to make a number easier for humans to understand.  Formatting is not at all helpful for computers and frequently causes unintended consequences.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.