Avatar of TCristiano
 asked on

Excel formula formatting


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.

Microsoft ExcelMicrosoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Saqib Husain

File not found
Saqib Husain

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

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

You never attached a sample...

Sorry about that - Long weekend. Attached is the example.
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Brilliant. That will work. Totally forgot about the VBA and send keys.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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.