Excel formula formatting

TCristiano
TCristiano used Ask the Experts™
on
EE-

I am having difficulty with number formatting in Excel.

The attached file contain a sample download (total list is 300,000 records) from our Cognos reporting system (column A). The data in these cells are formatted as numbers in Excel. However, if I import to Access, they are classified as text. In addition, performing calculations on these cells yields erroneous results (see columns B & C).

I did find that if you simply F2 in each cell in column A (replicated in column F), Excel recognizing them as values; calculations work fine and I can import into Access as values.

How can I replicate the F2 keystoke 300,000 times with or without VBA? And why does Excel do this with certain downloaded data?

Thanks in advance.

Regards,
T
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
File not found
Enter zero in any blank cell
select the cell and press ctrl-c  (copy)
select your numeric text cells
choose paste-special
select option Add
ok

Commented:
Another way is to use =Cell value x 1 in another column. It'll convert that value to numeric. Then copy and paste or drag the corner down the columns and all will be numeric.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
TCristiano,
You never attached a sample...

Author

Commented:
Sorry about that - Long weekend. Attached is the example.
EE-Example.xlsx
MIS Liason
Most Valuable Expert 2012
Commented:
The issue here is that the cognos Date is interpreting the Fractional data as a string.
I am sure there is a better way, ...but you can run code like this in Excel to "Press F2" in all of the rows.

Range("a1").Select
 Do Until IsEmpty(ActiveCell)
    SendKeys "{F2}", True
    SendKeys "{Enter}", True
Loop

Author

Commented:
Brilliant. That will work. Totally forgot about the VBA and send keys.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
LOL!
Like I said, there is probably a better way, ...but I ran into this exact same issue "years" ago, and that is how I solved it.

Glad I could help.

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial