TCristiano
asked on
Excel formula formatting
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
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
Last Comment
Saqib Husain
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
select the cell and press ctrl-c (copy)
select your numeric text cells
choose paste-special
select option Add
ok
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.
TCristiano,
You never attached a sample...
You never attached a sample...
ASKER
Sorry about that - Long weekend. Attached is the example.
EE-Example.xlsx
EE-Example.xlsx
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Brilliant. That will work. Totally forgot about the VBA and send keys.
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
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
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
