Numeric to text conversion

I recently upgraded from Office 2000 to Office 2002. In 
2000, when copying from Access tables and pasting into 
Excel, the data type integrity was maintained, that is, 
text, date, and numeric fields came over into Excel in 
that defined format. With 2002, all my numneric fields 
are converted to text and I'm flagged that numbers are 
stored as text from the error checking function. 
Converting is a major headache and exporting the Access 
tables into Excel, though this does maintain data 
integrity, is not what I need to do in most cases. 
Question is, is there a way to modify defaults in Excel 
and/or Access 2002 to make it behave like 2000 as far as 
copying and pasting data from Acess tables to Excel 
spreadsheets?

0
anonymous (74722)
8/19/2004 3:39:41 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
395 Views

Similar Articles

[PageSpeed] 38

Debra Dalgleish posted a manual fix and a macro (from Jon Peltier):

http://groups.google.com/groups?threadm=3F4D3D5B.3020101%40contextures.com

Dave wrote:
> 
> I recently upgraded from Office 2000 to Office 2002. In
> 2000, when copying from Access tables and pasting into
> Excel, the data type integrity was maintained, that is,
> text, date, and numeric fields came over into Excel in
> that defined format. With 2002, all my numneric fields
> are converted to text and I'm flagged that numbers are
> stored as text from the error checking function.
> Converting is a major headache and exporting the Access
> tables into Excel, though this does maintain data
> integrity, is not what I need to do in most cases.
> Question is, is there a way to modify defaults in Excel
> and/or Access 2002 to make it behave like 2000 as far as
> copying and pasting data from Acess tables to Excel
> spreadsheets?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/19/2004 9:25:41 PM
And she more recently posted this <g>:

'=============================
If you're using Excel 2002, the problem with Access data has been fixed 
in Office XP Service Pack 3. There is information in the following MSKB 
article:

Numbers that are copied from Access 2002 paste as text in Excel 2002
    http://support.microsoft.com/default.aspx?id=328933
'==========================

And there are a variety of methods for converting text to numbers here:

   http://www.contextures.com/xlDataEntry03.html


Dave Peterson wrote:
> Debra Dalgleish posted a manual fix and a macro (from Jon Peltier):
> 
> http://groups.google.com/groups?threadm=3F4D3D5B.3020101%40contextures.com
> 
> Dave wrote:
> 
>>I recently upgraded from Office 2000 to Office 2002. In
>>2000, when copying from Access tables and pasting into
>>Excel, the data type integrity was maintained, that is,
>>text, date, and numeric fields came over into Excel in
>>that defined format. With 2002, all my numneric fields
>>are converted to text and I'm flagged that numbers are
>>stored as text from the error checking function.
>>Converting is a major headache and exporting the Access
>>tables into Excel, though this does maintain data
>>integrity, is not what I need to do in most cases.
>>Question is, is there a way to modify defaults in Excel
>>and/or Access 2002 to make it behave like 2000 as far as
>>copying and pasting data from Acess tables to Excel
>>spreadsheets?
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/19/2004 10:50:15 PM
That should make life a little easier for the OP.

(I hope I can remember it when/if I respond to an Access question again!)

Debra Dalgleish wrote:
> 
> And she more recently posted this <g>:
> 
> '=============================
> If you're using Excel 2002, the problem with Access data has been fixed
> in Office XP Service Pack 3. There is information in the following MSKB
> article:
> 
> Numbers that are copied from Access 2002 paste as text in Excel 2002
>     http://support.microsoft.com/default.aspx?id=328933
> '==========================
> 
> And there are a variety of methods for converting text to numbers here:
> 
>    http://www.contextures.com/xlDataEntry03.html
> 
> Dave Peterson wrote:
> > Debra Dalgleish posted a manual fix and a macro (from Jon Peltier):
> >
> > http://groups.google.com/groups?threadm=3F4D3D5B.3020101%40contextures.com
> >
> > Dave wrote:
> >
> >>I recently upgraded from Office 2000 to Office 2002. In
> >>2000, when copying from Access tables and pasting into
> >>Excel, the data type integrity was maintained, that is,
> >>text, date, and numeric fields came over into Excel in
> >>that defined format. With 2002, all my numneric fields
> >>are converted to text and I'm flagged that numbers are
> >>stored as text from the error checking function.
> >>Converting is a major headache and exporting the Access
> >>tables into Excel, though this does maintain data
> >>integrity, is not what I need to do in most cases.
> >>Question is, is there a way to modify defaults in Excel
> >>and/or Access 2002 to make it behave like 2000 as far as
> >>copying and pasting data from Acess tables to Excel
> >>spreadsheets?
> >
> >
> 
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/19/2004 10:58:43 PM
Reply:

Similar Artilces:

Blocking e-mail based on message text.
Hi! I've got a question concerning e-mail blocking. I'm using Exchange 2003 and TrendMicro MailScan for antivirus. Antivirus removes infected attachment, but lets the letter pass through. There's no "remove e-mail function", so the users have been complaining about those particular letters, saying "Your account has been blocked". Is there a way to configure Exchange to delete e-mail based on the subject (e-mail text)? I know that you can block it in outlook, but that's the last resort. Thank you. "Igors Belijs" <IgorsBelijs@discussions.mi...

combo box text is blank
I have a timesheet form. I have fields jobNumber and WorkCodeID. JobNumber has a description which displays only when selecting from the combo box. The job number displays on the form. The workcodeID works a little different. The workcodeID has a description which I want to display on the form, not the id. The code for my workcodeID combo box says display the description not the code, but bind the combo box workcodeID to the form workcodeID. I hide the workcodeID setting the column widths of the combo box to be 0cm;5cm etc. Now when I load the form, the historical records for w...

How do I get incoming .jpg's to show up in my text body
I'd like to be able to view my .jpg's in my text body and not have to click on them individually tp open them. I've Already loosened my security settings Are the mail items in question HTML mail items? "Scott" <Scott@discussions.microsoft.com> wrote in message news:4A5ACCCA-EEC8-4428-8EF7-E379DD0672AB@microsoft.com... > I'd like to be able to view my .jpg's in my text body and not have to > click > on them individually tp open them. > > I've Already loosened my security settings Hi Vince, I don't know that they are HTML Items...

Remove redundant text
How would I remove in a query the last or right most characters or a constant value like: New York, NY Rochester, NY Buffalo, NY And I want to remove ", NY" Thanks in advance for the help The below function will return everything except the last 4 characters of a string. Left([FieldName], Len([FieldName]) - 4) Rich - Back up your table or database before updating your data. You will use an Update query. You will need to know what you are removing. If it is ALWAYS the last 4 characters of the field, then your update would look like this: UPDAT...

Conversion of Data in Tables
Trying to do some data mapping of user information from our old system to our new one. Structure: 1 Main Tables (old system) - UserFunctionLocation (Contains 3 fields: UserID; Function; Location) 2 Conversion Tables - ConversionFunct (Contains 2 fields: OldFunction; New Function) - ConversionLocation (Contains 2 fields: OldLocation; New Location) Need a query that converts entries within Table 1 (Function and Location fields) to be as per the Conversion table fields. I can do this when it's just one on one mapping by linking the tables, however this will mean that if there's no c...

how to add text box
Is it possible to add a text box to a document, but without the black border around the text box? It is. Double-click the border of your textbox, then, under "Line", select the colour you'd like to use, or simply use "No line" to get rid of the lines altogether. On 29/11/06 9:45, in article 1164789931.183526.112550@h54g2000cwb.googlegroups.com, "lawengin" <skho@iinet.net.au> wrote: > Is it possible to add a text box to a document, but without the black > border around the text box? > -- Michel Bintener Microsoft MVP Office:Mac (Entourage...

Entering left & right aligned, & centred text, into one merged cel
I am using Excel 2003. I have a spreadsheet with 8 (eight) columns. I have used the header and footer, and have entered left aligned, centred, and right aligned information in both. I have merged all the cells in rows 1, 2, and 3, into one cell. I would like to enter information in that cell on the left, centre, and right, i.e. "Name" on the left, "Subject Matter" in the centre, and "Date" on the right. Is there a way to do this? Thanking you for your assistance. Simba Hi Simba, No, don't think so. Do you really need to combine the info into one ce...

Help
I have a monthly process where I bring a range of cells into a spreadsheet (copy-paste) and I need the information to be numbers but it is created (out of my control) as text such as... '1.23 '1.24 '1.25 and so on for about a hundred rows It is really cumbersome to have to keep pressing F2 to edit, home to get to the ' and then delete and enter to remove the ' mark and convert the text to a number I used the Macro recorder (with relative value set) but it picks up the absolute value of the first cell and then when I run the macro it puts that value into each cell What I...

need help sorting text by trailing Numbers Value
I am looking for some help sorting a text field (PartNumbers) alphabetically but correctly depending on the value of the ending few charachters if theyre numbers.. Here is some sample data an960pd10 an960pd300 an960pd6 i would like it sorted like this an960pd6 an960pd10 an960pd300 any ideas? Thanks Barry I looks like your values have a fixed length to the left of the numbers. If this is true, you can use the following in the Sorting and Grouping dialog expression: =Left([PartNumbers],7) =Val(Mid([PartNumbers],8)) -- Duane Hookom Microsoft Access MVP ...

I want to inhibit editing of one text box based on the content of another
The two text boxes are on the same form (subform3). I want to inhibit the Quantity filed from being edited if the Status field contains "Ship Requested". The following code gives me an error 'Expected: list separator or )' Private Sub Quantity_BeforeUpdate(Cancel As Integer) ' Inhibit txtbox if the Status field is "Ship Requested" IIf(me.Status = "Ship Requested"; me.AllowEdits = True, _ me.AllowEdits = False) End Sub On Fri, 27 Nov 2009 23:07:21 -0500, BobC wrote: > The two text boxes are on the same form (subform3). >...

Word/Outlook 2002 email merge as plain text
I am trying to do an e-mail merge with Word and Outlook 2002 (all service patches installed). I use the mail merge wizard in Word and select recipients from Outlook through the merge wizard. When I choose plain text, the e-mail sends with nothing in the body of the e-mail and attaches the Word document. HTML works fine. I need to send the e-mails as plain text because the news outlets on my media list prefer plain text or the e-mail gets caught as spam. Any suggestions to get the messages to go as plain text? This used to work and now it doesn't! ...

Importing via a text or csv file
I am importing via csv file, however i have some fields which a numbers with leading zeros. Excel seems to remove these leading zeros. Is there anyway to import without removing the zeros? The usual way of handling this is to custom format the Excel cells wit someting like 000000 to show the zeros -- Message posted from http://www.ExcelForum.com Step 3 of the text import wizard allows you to choose the formatting of each column of data i.e. text This will import the numbers leaving the leading zeros intact. "Jason Frost" <jpfrost@ntlworld.com> wrote in message news:96...

Problem of viewing the text in Visio 2003
hi all, I've a visio 2003 doc with most of the texts in font size of 6pt or 5pt. I setup to 150% zoom to view it. It has no viewing problem so far. However, when I open up the doc today, all the texts are displayed like a rectangular block. I can't see each character at all. Only the text in 10pt or higher can be display correctly. Or if I increase the zoom to 300%, then I can see the texts. And I found that all of my docs have the same display problem. Can anyone help? Many thx. With me it works. Has the font changed, or the display settings in the control panel ? Changing ...

Report Text box dilemma
The dilemma I have is a text box "tbCurrent0" on my report "rptOwnerPaymentMethodBatch" Ive created another report "rptOwnerPaymentMethodBatchA" and everything works except this text box below, how does it know only to work with "rptOwnerPaymentMethodBatch" . all i have done differen with this report is change its report "name" and some text boxes from =DLookUp("CompanyPhone","tblCompanyInfo") to =DLookUp("CompanyPhone1","tblCompanyInfoA"), ------------------------------------------------ ...

Combo Box to enter data to text box!
In my subForm I have a Combo box drop down list I want to have the data in my combo box entered into my Text Box [tbAdditionCharge] on AfterUpdate -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 On Fri, 1 Jan 2010 11:49:12 +1300, "Bob Vance" <rjvance@ihug.co.nz> wrote: >In my subForm I have a Combo box drop down list I want to have the data in >my combo box entered into my Text Box [tbAdditionCharge] on AfterUpdate Since data is not stored in combo boxes, textboxes, subforms or ...

email broken up into multiple attached text files on emails with multiple images
Hi There, I am encountering a fairly annoying problem with emails I am receiving with Outlook 2003 and that that have been sent from a Macintosh using the latest version of AppleMail. The emails have multiple image attachements (all coming though fine, although not in line which is how they have been sent) but the text of the email is broken up into text files that are attached. The first section of text in the email (ie before the first image) comes through fine, but the text after the first image in in an attachment, then the text after the second image is in another, seperate at...

Text to Number
Is there an automated way in which I can convert a text data type to a number data type? Please Help Hafeez Esmail Check out the Val() function. -- Cheryl Fischer Law/Sys Associates Houston, TX "Hafeez Esmail" <no@spam.com> wrote in message news:002d01c38f3a$1bb41e10$a101280a@phx.gbl... > Is there an automated way in which I can convert a text > data type to a number data type? > > Please Help > > Hafeez Esmail Try CInt(stringVar) [Convert to Integer function] CLng(stringVar) [Convert to Long Integer function] >-----Original Mes...

vlookup using text to refer to other workbooks
ok i'll try and explain as clearly as possible in column b week commencing dates, eg 05/10/03, 12/10/03 in column c are id's that are drawn from a cell that the user enters. so i need to lookup the id and return the x column in a workbook that is named with the week commencing eg C:\data\20031005.xls is it possible using text and concatenate functions, or vb, to use vlookup to look in the correct workbook dependent on the date in column b. I hope i've explained clearly. I've been working on this for some time, and hopefully the knowledge in this group could provide a solution. ...

output to the same line in a Text file?
hi there How do I output to the same position in a Text file everytime? I want to dump data for a given Date so I'd like to put it in the same row for that Date everytime, and so overwrite the existing data in that row? I'm using the following command: Write #3, logfactor(dateline, 1), logfactor(dateline, 2), logfactor(dateline, 3) 'where dateline is today's date in Excel date code e.g. 40010 I want to be able to overwrite the existing data for line 40010 in my text file, and replace it with new data. Hope that it clear!!!! thank you Bernard. If, a...

My text in a cell disappears when I add more later on in Excel
While in Excel when I want to make corrections or add something in a cell later on, all text disappears when I try to insert the new text Excel is set up to overwrite data in a cell. There are two ways around this: 1. You can double-click in the cell that contains the data you want to change or add to, and then place your cursor in the place you want to begin typing. 2. Select the cell in which you want to change/add content to and then click in the formula bar (where the cell content is displayed) in the place you want to change/add content. *Remember to click "yes...

endnotes before text
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel hi. in order to set endnotes before bibliography, I should enter Format>Document and click on Layout tab, and check the box for "suppress endnotes". <br><br>I can't fint format - document nor layout options under format. i am using word for mac 2008, version 12.2.4 <br><br>can you help me? Are you trying to say that the word "Format" does not appear in the main Menu Bar? If that's the problem go into the Customize Menus & Toolbars dialog. On the Tool...

Plain Text email unreadable
I previously used WinXP & O2K3. Recently I reloaded my machine with Vista & O2K3. New installation, not upgrade. Ever since the switch, emails from one specific sender are being received and are unreadable. The emails are in plain text and the header is readable but the body of the emails are garbage. I have viewed the emails via webmail and they look fine. when I open outlook and download the messages into my local PST they are unreadable. I am using the same version of O2K3 and can't figure this out. I am able to receive plain text emails from other senders ...

MFC SDI Exe to OCX conversion problem
Hi, I have used below link for MFC SDI Exe to OCX conversion problem. Its well-known link of scribble example. http://www.microsoft.com/mind/0497/mfc.asp In this example , framewindow is used as WS_CHILD so it doesnt give menu bar. But I need menu bar so I have made WS_POPUP style. But the program crashes in CDocument destructor when I close IE . In this statement: m_pDocTemplate->RemoveDocument(this); I am unable to understand why it happens. Please help. Thanks, Dhaval ...

Linking of Cell value to a text box
Is it possible to refer a cell and/or more than one cell to a text box? If yes, please tell me how to do that. In case it is possible to refer more than one cell to a text box, then it would require flexible text box. I mean when more data is required to be put in the same text box, its boundaries should be flexible to accommodate the data of more than one cell that have been referenced/linked to the text box. Please Help. Thanks in advance Sudhanshu --- Message posted from http://www.ExcelForum.com/ If you want the value of a cell to appear in a textbox and be linked such that if the v...

vertical center alignment of text in Edit box
Hi I want to vertically center align the text in a edit box (CEdit Object) as text is getting entered in the text box irrespctive of the font size. Can anybody help in this matter? As far as I can tell, this is impossible. There is no support for it. I once had someone describe a means of doing this: you use DrawText and use the vertical-center option in a plain CWnd. Then, if someone clicks the mouse on the text, just like a spreadsheet, you create a borderless edit control at that instant that has the same text in it, and this is placed so the effect is invisible to the user. In the O...