Solved

Excel Text to Number Formatting

Posted on 2012-03-27
8
343 Views
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
0
Comment
Question by:DMKetcher
  • 5
  • 3
8 Comments
 
LVL 45

Expert Comment

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

Author Comment

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

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.
0
 

Author Comment

by:DMKetcher
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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

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.
0
 

Author Comment

by:DMKetcher
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?
0
 

Accepted Solution

by:
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.
0
 

Author Closing Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Problem 12 48
VLOOKUP Function MS Excel 2010 2 21
TT Master Button 18 16
Added a column screws up code 5 15
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now