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
410 Views

Similar Articles

[PageSpeed] 19

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:

My text seems stuck in the text box! I can't make changes!HELP!
I am trying to alter text just typed into a text box using Publisher 2003. For some reason, the system will not allow me to go back to edit. Any ideas? Thanks Is your application activated? Is it just this one text box in this publication? Are you working with a template? Select the text box, copy, paste, can you edit the pasted text box? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Learner" <Learner@discussions.microsoft.com> wrote in message news:68E3A7B3-BB72-4D73-9250-BF4F3FAE2C08@microsoft.com... >I a...

Can I change the font color of text inside an IF statement?
Here is the equation I'm using in Excel 2003. =IF(AND(G3="NO",B1-E3<2),B1-E3,IF(AND(G3="YES",B1-E3<=96),B1-E3,"Error")) I would like the text color to change red if: IF NO is selected and B1-E3 >2 I would like the text to turn red. IF YES is selected and B1-E3 >4 I would like the text to turn red. If I can do this in conditional formatting, that’s fine, I'm just not familiar enough with excel to do this. Thanks for the assistance! Craig Hi a formula can't change a format. But have a look at 'Format - Conditional Format' fo...

Best way to store image, voice, text file?
Hello there, Is it possible to define a sql server field to store image, or voice, or text file? If the size of the file is very big, thus exceeding the max row size 8064 bytes limit, is it better to store it in a directory, and just save the location of the file in the field? Thanks, Ben On Thu, 1 Jul 2010 07:19:54 -0700, Ben <Ben@discussions.microsoft.com> wrote: >Hello there, > >Is it possible to define a sql server field to store image, or voice, or >text file? > >If the size of the file is very big, thus exceeding the max row size 8064...

copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes
i have a formula composed of 3 lines in one cell: =" IF c_MSISDN_NDC" & $E3 & " IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 1, c_oper_name);'); utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 2, c_oper_name);');" this produces a text string, e.g. as below: " IF c_MSISDN_NDC70 IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_t...

Keep quoted numeric characters as text.
Is there any way I can keep Excell from converting double quoted strings of numeric characters to a number when opening a .csv file? I have a column where sometimes the text being imported is something like "001". I need to load it as text '001' instead of as a number 1. If I change the column to text after opening the file, the leading zeros are still missing. In MacXL you can open it directly with Text Import wizard. With WinXL you need to change the extension (say, to .txt) first. Then set the column to Text in the third pane of the wizard. In article <043b...

XML error "Some Data Was Imported As Text"
Daily, I recieve a 3rd party XML file that I parse in Excel (using an excel macro) and then import into an Access database. Infrequently, the data will import into Excel, then parse and then the routine stops with the error message above. I look through the raw XML file and everything appears to be within the brackets, etc. Can someone give me some clarification to the meaning of the error message? Thanks in advance and Mery Christmas and Happy Holidays! The error message might be generated within your macro - as it is parsing the data it might detect that some field which should be ...

Remove text from a cell
Hi there I have some data in a spreadsheet which is an output from another program. One column has a series of numbers in it, each with the suffix of "kb" and I want to remove the kb from each cell so that I can sort by number. Is there an easy way to do this without having to edit each cell individually (I have about 600 rows!) thanks Trace Hi one way: Use 'Edit - Replace' the other way: use the formula =--TRIM(SUBSTITUTE(A1,"kb","")) in a helper column >-----Original Message----- >Hi there > >I have some data in a spreadsheet which is ...

text in charts
hi all.. Is it possilbe to have a textbox in a chart, that actaully corresponds to a cell entry in the spreadsheet, to change its contents for whatever I type in the cell? is it possible for the title too? thanks!! To add worksheet text to a chart: 1. Click on the chart to select it. 2. Type an equal sign (you'll see it appear in the formula bar) 3. Click on the cell that contains the text for the chart 4. Press the Enter key. 5. Position the textbox where you'd like it in the chart, and format as desired. tony wrote: > hi all.. > > Is it possilbe to have a te...

Text Manipulation from PDF
Hi Really weird problem I have with a report generated by a application at work. I run off a monthly report of products completed by employees. It shows the following info in a pdf report User ID No of Products Date Sub Date Last Sub Last Date Sub Smith_0001 5 01/01/2006 10/01/2006 10/01/2006 Brown_0002 8 02/01/2006 11/01/2006 11/01/2006 Etc but it lists about 300 staff when I copy it and paste it int...

Adding text and date problem
I have text in column 1, a date in column 2 and in column 3 I wish to show the text followed by the date. I have tried both Concatenate and & but the the result is text followed by the underlying number representing the date. I cannot seem to format this column to show the date as dd/mm/yy. Any suggestions? Regards ="Today is: "&TEXT(A1,"mm/dd/yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel "newman" <aa111@despammed.com> wrote in message news:464C1646.AC6A42E0@despammed.com... |I have text in column 1, a date in column 2 and in column...

Sorting Alpha-Numeric Values
I would like to sort data that have aplha numeric values. When I use the sort command, the numbers wind up getting sorted by place values instead of the actual value of the number, kind of like alphabeticaly except with numbers instead of letters. For instance, I would like to these numbers sorted like this: 17A 36B 265F 1492C 1609A Instead, Excel sorts them like this: 1492C 1609A 17A 265F 36B It appears that the suffixed alpha characters alter the way the numbers would normally be sorted. Any ideas how I can sort them more logically/numericaly? Thanks! Larry ...

How to full justify text ?
Help! I have tried many things but can't figure out how to full justify text. Betababy, Format, Cells, Alignment, Under Horizontal, choose Justify. -cheers -- Angel Bivins WordAngel Document Design www.wordangel.com "Betababy" wrote: > Help! I have tried many things but can't figure out how to full justify text. > ...

Text and backgound do not print
I have created a folding leaflet which appears fine on the screen. However, when I click on print preview, half of the leaflet appears only as a white space. When I print it, I get the same result, i.e. only half of it appears on the page. Can anyone help, please? Try running Design Checker on your leaflet. Sometimes issues will show up that that prevent items from being printed. I just helped a woman with a very similar issue. I deleted some extra text boxes and it's fine. ;-) -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/...

Text emails do not format
All of my incoming emails in Outlook 2003 are coming in as one long line of text. I've gone into my email options and turned off "Remove extra line breaks from text emails" but the problem still occurs. Does anyone know how to prevent my incoming text emails from turning the entire message into one long paragraph? Thanks! Fury Maybe the issue is with the person (or people) sending it. As a test, log into your webmail account, send yourself and email and then go into Outlook 2003 and see what it does... >-----Original Message----- >All of my incoming emails in ...

Receiving messages with no text.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop When receiving new messages, I can view content and attachments on some <br> mails but not on others. Subject bar and name is always visible. There's no <br> pattern either as to which mails work and which don't. Have rebuilt <br> database, but with no apparent success. On 1/26/10 9:35 AM, emet51@officeformac.com wrote: > When receiving new messages, I can view content and attachments on > some mails but not on others. Subject bar and name is always vis...

converting checkboxes to a text value
I have an existing database that employs checkboxes. I'd like to create a report that displays those boxes as "yes/no" text values, so that I can export them to an .rtf format, and have them actually show up. I'm a novice, so any help is greatly appreciated. The purpose of this is to be able to email specific pages of the report. I'm also open to other ideas for making this possible, if I'm going about this the wrong way. Thanks in advance. Charitycase wrote: > I have an existing database that employs checkboxes. I'd like to > create a report that...

Importing text using get external data
I have created a query spreadsheet that gets certain data from another spreadsheet (call it data spreadsheet). The data spreadsheet has a column where text is stored, which in some cases exceeds more than 255 characters. The query that imports such data truncates the text to the first 255 characters. Is there a way around it ? Any input will be appreciated. --- Message posted from http://www.ExcelForum.com/ ...

Change "Y" axis from integer to text
Excel 2k Is it possible to change the range on the Y axis from numbers (0-50-100) to text (low-medium-high) without just placing text boxes on the chart? Please let me know how!! You can simulate the effect. See http://www.peltiertech.com/Excel/Charts/DummySeries.html#VertCatAxis -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <077501c48c13$dde7a6b0$a401280a@phx.gbl>, anonymous@discussions.microsoft.com says... > Excel 2k > > Is it possible to change the range on the Y axis ...

Plain Text in outlook 2000
In Outlook 2000 with SP2 installed we are unable to send e-mail in plain text. For example: I changed all the setting within outlook to send and receive e-mails in plain text. Every time I open a new one or reply it reads a rich text not plain text. I am able change it manually to plain text but we want it to automatically do it in plain text. We have tried all the settings inside Outlook and the recommend change to the registry: The mailto protocol for IE is set to OUTLOOK.EXE -c IPM.Note /m "%1" Still does not work Anyone have any Ideas Outlook Profiler can set thi...

How to combine text from 3 cells into 1 cell with space and ", bet
How to combine text from 3 cells into 1 cell with space and ", between. Is it a formala I can use? HI Try this =A1&" "&""""&B1&" "&""""&C1, adjust range to your needs HTH John "JOF" <JOF@discussions.microsoft.com> wrote in message news:3BD2A42E-BCB9-4504-8454-727E366E25DD@microsoft.com... > How to combine text from 3 cells into 1 cell with space and ", between. Is > it > a formala I can use? ...

excel
I am trying to write a macro to save a excel spreadsheet and use a cell that contains text like a customer name. Record your macro to include the File > SaveAs, then edit that line of code. Instead of ... Filename:="\\FinancialReporting\FY2006" ..., you might use .... Filename:="\\FinancialReporting\FY" & range("B2").value ... "bossman tv" wrote: > I am trying to write a macro to save a excel spreadsheet and use a cell that > contains text like a customer name. ...

Forcing Outlook 2003 Not To Use Rich Text (WINMAIL.DAT)
Hi there, I'm having a problem with my Outlook 2003 (which many others appear to be having as well) whereby Outlook insists on sending some messages as Outlook Rich Text Format, and of course non-Outlook recipients receive a message with the infamous WINMAIL.DAT attachment. I had imported my Contacts from Outlook 2000, and I believe that somewhere during the export & import process, the Internet format for the e-mail addresses of all my contacts (which you can view in the E-mail Properties dialog box, by double-clicking a contact's e-mail address) was set to "Send using Outl...

HOW DO I ADD NON-NUMERIC VALUES
HOW DO I ADD NON-NUMERIC VALUES? Without shouting (i.e., using ALL CAPS), please explain what you mean by "add non-numeric values". Addition normally requires that values be numeric. In article <4DB070A2-B74F-44EA-8FDF-A49C20AE691A@microsoft.com>, DUKE <DUKE@discussions.microsoft.com> wrote: > HOW DO I ADD NON-NUMERIC VALUES? Can you give examples of the "non-numeric values"? (btw, we avoid typing in all capitals in the newsgroups.....it indicates shouting) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "DUKE...

Text colours printing wrong
(Publisher 2003 and Windows XP). I have just installed Office Professional and am trying to use Publisher 2003. However the printed text colours do not match the design colours at all making it totally useless! Fills and pictures are ok. It is as if each font color is being translated to a new colour. This is easily seen in Print Preview. Even when I us a supplied template without modification this happens. Tried different colour schemes with similar results. Example in the Nutmeg scheme a dark crimson prints as a bright flame red and the khaki prints as bright yellow. If I convert to a web pa...

macro to find phone number and label with text
Hi-- Here's the situation: I have long lists of satellite phone bills that I need to track by individual phone users and I want to build a macro that seeks out a specific number (example, macro searches "882 156 445 4554" and in the column next to it, automatically spits out "Dan's phone." In one Excel file, I have a growing list of satellite phone numbers (~400 numbers) that I'm constantly adding to that I'm hoping the macro can draw from. In the other set of files, I have individual phone bills for each phone (about 18 separate excel files per month) wi...