Excel Text to Number Formatting

Posted on 2012-03-27
Last Modified: 2012-04-16
Hello again,

I am running into problems with numeric data when I import it into MS Access, and export back to Excel. It converts the numeric data to text. I found solutions for this on the internet but the only one that seems to work is to go to the Error Checking button and manually tab through every field in the spreadsheet until all are converted. There has to be a better way. Using the standard method of formatting does not work for this. I have to perform calculations on this data  and the spreadsheet is going to get very large and this method will be time consuming and tedious. Can someone assist?

Thanks, Janis
Question by:DMKetcher
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 47

Expert Comment

by:Martin Liss
ID: 37774227
What is the format of the field in Access that receives the data?

Author Comment

ID: 37774231
I am importing excel spreadsheet data into an empty unformatted table. It converts when it imports. Does that help?
LVL 47

Expert Comment

by:Martin Liss
ID: 37774243
Do you have to do it that way? I've never done what you're doing but I assume that if the field in Access were numeric that you wouldn't have a problem.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 37774258
It is the only way I know of to import data from Excel. Is there a better way? I always got to MS Access, External Data, Import & Link, Excel - and then use the wizard to bring in the file and it saves as a table. Normally the data saves as the same data type. I am working with currency in excel so that could be part of the problem.
LVL 47

Expert Comment

by:Martin Liss
ID: 37774296
In Access create a table that contains a Currency field and then use the Access wizard to import into that table.

Author Comment

ID: 37774308
OK I will try it and get back to you. My workbook data originations in Excel and contains numerous numeric fields. I don't want to have to figure out the layout from the point of view of access without a template. Is there an easy way to use an Excel template and then format each field in Access?

Accepted Solution

DMKetcher earned 0 total points
ID: 37833997
I did not have unique field names. That was the problem. This is closed. Thanks for all of the comments.

Author Closing Comment

ID: 37850351
It was the true answer. I figured it out.

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
EXCEL VBA Code to get API Data and output to excel table 6 107
create different version if file exists 1 45
Batch convert csv to xlsx 10 60
copy down array 24 36
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question