AutoFormat - help!!!

OK We often export mailing data from our database into a CSV file which
we then open in Excel to send to our bulk mailing contractor.

A problem has recently come to light however.

US Zip codes starting with a zero - Excel automatically chops the
leading 0 off - leading to returned magazines for us.

Is there any way to stop Excel automatically guessing the format of the
cell and chopping the leading zero off?

I don't want to have to change our data by adding a - or something to
kick the cell into text format - Just want to stop excel ruining our
data automatically.
I prefer to do that myself!

Any ideas?

Using Excel 97 on windows 2000

Thanks
0
whatever1 (18)
4/14/2004 9:51:27 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
443 Views

Similar Articles

[PageSpeed] 35

Hi

Try changing the file name from .csv to .txt  Excel will then fire up the
import wizard when you try and open it and you can tell the program that
this particular column is text.

-- 
Andy.


"Batfastad" <whatever@noway.com> wrote in message
news:zQ7fc.32759$Y%6.4145543@wards.force9.net...
> OK We often export mailing data from our database into a CSV file which
> we then open in Excel to send to our bulk mailing contractor.
>
> A problem has recently come to light however.
>
> US Zip codes starting with a zero - Excel automatically chops the
> leading 0 off - leading to returned magazines for us.
>
> Is there any way to stop Excel automatically guessing the format of the
> cell and chopping the leading zero off?
>
> I don't want to have to change our data by adding a - or something to
> kick the cell into text format - Just want to stop excel ruining our
> data automatically.
> I prefer to do that myself!
>
> Any ideas?
>
> Using Excel 97 on windows 2000
>
> Thanks


0
andyb1 (494)
4/14/2004 10:41:05 AM
Andy B wrote:

> Hi
> 
> Try changing the file name from .csv to .txt  Excel will then fire up
> the import wizard when you try and open it and you can tell the
> program that this particular column is text.

I have found another way, making sure that when we export the csv, that
the zip field adds a "-" onto the end of the Zip code, making Excel
think it's text.
Not ideal but hey!

Thanks anyway

Batfastad
0
whatever1 (18)
4/14/2004 10:54:14 AM
I would suggest using the Get External Data tool in the Data menu. Yo
can import a text file from here and it will allow you to specif
formats for a csv file. You might need the MS Query add-in if th
option isn't in the menu.

You may also want to look at the database and get speech marks (" "
put round any items you want to be formatted as text. This will sav
you having to go through the get external data method each time.

Dunca

--
Message posted from http://www.ExcelForum.com

0
4/14/2004 11:02:14 AM
To fix your existing data to make them all text,  I have a macro
   Fix up for 5 digit US zip codes   (#fixUSzip5)
   http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

If you need assistance to install or to use a macro please refer
   to Getting Started with Macros.
   http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Batfastad" <whatever@noway.com> wrote ...
> OK We often export mailing data from our database into a CSV file which
> we then open in Excel to send to our bulk mailing contractor.


0
dmcritchie (2586)
4/14/2004 12:07:10 PM
Reply:

Similar Artilces:

HELP NEEDED!! Link cells between worksheets
Hi , I have a workbook contains 1+50 worksheet, I want to link each sheet number to sheet one, besides manually change the sheet # in the formula =SUM('sheet02'!$H$16:$H$21,'sheet02'!$H$26) =SUM('sheet03'!$H$16:$H$21,'sheet03'!$H$26) =SUM('sheet04'!$H$16:$H$21,'sheet04'!$H$26) =SUM('sheet05'!$H$16:$H$21,'sheet05'!$H$26) ..... to sheet 50... Can someone help me to automatic it in anyway? Thank you very much!!!! Nicole Try this... =SUM(INDIRECT("'Sheet"&TEXT(ROWS(A$1:A2),"00")&am...

Unable to open html attachment in outlook 2000 (Please help)
Currently using outlook 2000. Got an attachment html link. Click open, a new windows --> system32 opened. Click onto the attachment again, Click save as, into my computer, double click the link, then it able to open in IE. Q: Why can't I open it directly, instead I have to save the attachment before open the html file. Please help. Thanks & Regards James something is messed up if it's opening a windows explorer window. I think you'll need to reregister IE. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for ...

HELP PLEASE #6
i HAVE JUST SET UP OUTLOOK... I got a memo today, a pop up that said that Outlook can get rid of your spam.I thought it was from Microsoft. Now I have another tool bar on my internet and I hate it. Worse than that, I keep getting pop ups. How do I remove it.It is called SpamBlockerUtility. Relamb111@aol.com thanks look for spamblockerutilityblockerutility "rhory" <anonymous@discussions.microsoft.com> wrote in message news:4a0d01c47396$e1bdd6a0$a301280a@phx.gbl... > i HAVE JUST SET UP OUTLOOK... I got a memo today, a pop > up that said that Outlook can get rid o...

XPath Help #3
I have the followign XML document format. <Asset> <App Name="Title" Value"Pigs" /> <App Name="Rating" Value"G" /> </Asset> I'm trying to figure out how to display the Title's value but being sorted by the Rating's value. So far I have. Displays title: Asset_Metadata/App_Data[@Name = 'Title']/@Value I've tried many other variations to get this to work with no luck. Any help would be greatly appreciated. * alien_attack@hotmail.com wrote in microsoft.public.dotnet.xml: >I have the followign XML docu...

Unable to display folder error message--HELP
Since having Windows XP reinstalled last week, I have a new problem with Outlook 2002. I've used Outlook for several months for contacts and calendar, which I hotsync with a Palm. I do not use Outlook for email, as I use Netscape. I am not on a network. This is the problem. Both the Calendar and Contacts work fine for awhile and then I get the following message when I either click on Calendar or Contacts: C:\Documents & Settings\username\Local Setting\Application Data\Microsoft\Outlook\Outlook.pst is already in use by the maximum number of applications. Close some of these app...

Help with Formula, returns #NAME!
Hello, I am trying to get this formula to work. =SUM(IF(D5,VALUE<"4/1/07", E5,+ SUM(IF(F5,VALUE<"4/1/07",G5)))) Thanks bp Try: =SUM(IF(D5<DATEVALUE("4/1/07"),E5)+SUM(IF(F5<DATEVALUE("4/1/07"),G5))) Jim May "bp" wrote: > Hello, > I am trying to get this formula to work. > =SUM(IF(D5,VALUE<"4/1/07", E5,+ SUM(IF(F5,VALUE<"4/1/07",G5)))) > > Thanks > bp > > > Hello Jim, Thanks, it worked well. Could you adjust the following formula to accomodate a DATE RANGE. example d...

Onclick? Help Auto Add
I need make a excel so that when i click a Button it will Add 1 to B3 etc So each time we sell a product we can just press + or - to add and remove a figure from that field Hi Adam, You can use a spinner to do that. Go to View>Toolbars>Forms On the forms toolbar select the spinner (it's an up and down broad arrow separated by a dash) Your cursor will now be a small cross, just click and drag where you want the button. Right click on the button and goto Format Control where you can set the cell link ($B$3 in your case), you can also set the incremental value as well as many other...

Help question answer please up grading from 2000-2003
I am updating from office outlook 2000 to 2003. I am not comp. savy but trying to do a safe update and need some advice. I backed up all office files, contacts e-mails etc to a PST. When I installed 2003 disk it asked me to remove 2000 first. So I asume the procedure I would do is to add/remove 2000 install 2003 and import back the PST. sounds easy I thought since this was a update that it did everything Automactily Should my 2000 PST back up restore to the 2003 office when installed ? If I remove 2000 I have no disks to reinstall if things should go south thats why I am ...

Help with IF formula
Hi everyone. Someone on here always has an answer for my question, so thanks in advance. I am running XP Pro SP2, and Office Pro 2003. Column A is for quantity, and the quantity can be positive or negative. Column H is for individual pricing, and Column I is for the price extension (i.e. A*H). Column I is blank unless there is a number to be displayed. I have the following formula set up in I4 now: =IF(A4>0,A4*H4,"") which works OK if A4 is a positive number. What should the formula be so it also works with a negative number? Rember that if A4 is blank, then I4 is also ...

Moving from Excel 03 to 07, need help with customizing
In Excel 03, we had a series of toolbars that contained buttons that ran a macro placing a picture on the spreadsheet in the selected cell. There were several toolbars as there were different sets of pictures in different colors. They were used as accounting tickmarks. I can change my macros to run on 2007 (due to 07 not liking the Select command) but I'm having troubles finding a substitute for the custom toolbars. The ones I had in 2003 did come over under the Add-Ins tab, but I cannot update them as needed. Plus there is no way to bring up a toolbar for the differ...

Pivot Chart/Report Help
I need some serious help here. I've been trying to figure this one out for 3 days now and I'm getting nowhere. I've searched google extensively for anyone else that might have been having this issue but come up with nothing. OK, I have a very simple pie chart created in Access 2003. It is basically just a form in which the default view is set to "Pivot Chart." If I view this on the screen, it looks fine. If I print out the form, the chart is upside down and backwards. If I print out the report in which the form is a sub-report, the chart is upside down and backward...

Email address book showing duplicates
System: Running Outlook from Office XP on Windows 2000. When I go to a new email message and click the "To" to bring up the address book, there are several duplicates of the same contact listed. These do not occur in the regular contacts folder. The default email address book is set to be the same as the outlook contacts folder. If I delete the one in the contacts folder both of them dissapear from the email address book. Anybody have any idea what is going on? Many Thanks, S Look more carefully. I doubt these are duplicates. The Outlook Address Book lists each electronic ad...

Help with TCP Server.
The code below appears to work, but it eventually freezes and I have to reboot the server. Killing and restarting the program itself doesn't work as the listener socket is still bound. I have put debugging statements in to verify that the ThreadPool threads are being released and the most thread pool threads that this code used was 3, even under heavy load. The problem appears to be when there are no clients for an extended period and then a client attempts to connect. At that point, the application freezes. I have left out the BankInfo object which contains the bank nam...

Please help me with my Excel
I have got a new lap top and I have a good antivirus and spyware. my pc works good but once I open Excel my CPU jumps up to %100 and the strange thing is that when I click on other programs my CPU comes back to %4. The more strange thing is that when for example I press F2 to edit a cell , CPU works good but when I press Enter, again it goes back to %100. Here our IT man reinstalled my MS-Office and he did some other exotic operations but there was not any slightest change and the problem is still existing and going on my nerve. Please scientists help me ! I work with Excel 7 hrs per day ...

Help in displaying in a cell
I had type in a number of words (< 400 word count based of microsoft works) in a excel spreadsheet, but the cell only display ############ wrap text option is checked, What's the exact problem? Using excel 2007 Is the cell formatted as Text? Apply a General format to it instead. Also, you can insert manual line breaks as you enter the text using Alt-Enter. Hope this helps. Pete On Jul 3, 9:59=A0am, "crapit" <biggerc...@yahoo.com> wrote: > I had type in a number of words (< 400 word count based of microsoft works= ) > in a excel spreadsheet, but the c...

Subquery help PLEASE! :P
Hi all. I'm about to throw in the towel on this subquery. I've checked it and rechecked it. OK, as an overview, I have two tables, one of names of industries (automotive, energy, etc) named tblIndustries. I have another table of stock market company symbols, called tblSymbolsMain. This table has an industry type as a field for each stock symbol. I've written a function (that works great) called MultiDayPerfClose that returns a performance in terms of percentage of a stock. What I'd like to do is get an average of performance returns for each industry. I'm usin...

Help, documents on desktop not loading
Hello, Hope you can help, I have saved some excel sheets to my desktop but when I double click to open all i get is the following message "cannot find the file c:/doc&sett......... make sure path and filename...etc although if i open excel and go through the file menu these open without a problem. I have went through all the usual routes and everything is pointing too the right place. Anyone got any ideas please Thank you in advance Sometimes one of these works when you're having trouble with double clicking on the file in windows explorer: Tools|Options|General|Ignore...

Help: Transferring sent items to IMAP 'sent' folder
Hi, Someone somewhere must know an easy fix for this. The University in which I work is looking to change it's default e-mail program for staff from OE6 to either Outlook XP or 2003. The problem we're trying to find a solution to is the transferral of sent messages to an IMAP 'sent' folder. There is a Microsoft KB article ( 198852) that instructs how to set up a rule using the 'Rule Wizard' which will perform the task. The article says it only applies to OL '98 but we've tested it and it works for OL XP. What we need to know now is how to automate the appli...

Need Help #9
I changed my connection name in Microsoft Office Outlook 2007 and now I can't send email from a wireless connection but when I plug into a local connection it works fine. I changed it back and it still won't send through a wireless connection. "Sandy" <meme@yahoo.com> wrote in message news:3506D8AD-E130-41FC-9126-75CCBCA2B9A1@microsoft.com... > I changed my connection name in Microsoft Office Outlook 2007 What exactly do you mean by "connection name"? Have you enabled SMTP authentication for the account already? For details see; http://...

Need help with Application.FileSearch change
If someone could help with the code that I am posting below I would greatly appreciate it. I am using Access 2007 and this feature no longer is supported Turf Private Sub File_Locations1() Dim Location As Recordset Dim i As Integer Dim count As Integer Dim NewName As Variant Dim NewPath As Variant Dim WkSpace As Workspace Set WkSpace = DBEngine.Workspaces(0) Set Location = CurrentDb.OpenRecordset("Files", dbOpenTable) With Application.FileSearch .NewSearch .lookin = "G:\Shared\JDE\JDE_docs\Aber\Aberd\SL\" ' .FileName = "*&q...

Unmatch query help
Hi, I have 2 tables with some numbers. Both tables have duplicates. I would like to return 2 unmatch queries. First occurance of TableA number should match with first occurance of TableB number. First occurance of TableB number should match with first occurance of TableA number. TableA 09875464560 09875464561 09875464568 09875464560 09875464567 09875464560 ------------------------ TableB 09875464560 09875464568 09875464560 09875464566 09875464561 09875464561 1st query Result: Remaining in A not in B 09875464567 09875464560 2st query Result: Remaini...

I need some help converting this to C++
[I would have asked this in a C++ forum but the one I found is empty!] System.IO.Directory.CreateDirectory(path); return System.IO.Directory.Exists(path); I want to bury this in a C++ Win32 Project. My effort so far (that does not work and I do not understand the error messages) is: #using <mscorlib.dll> using namespace System; using namespace System::IO; bool* _clrcall;bool CDirectory(String path) { DirectoryInfo d = Directory::CreateDirectory(path); return (d.Exists(path)); } I get Error 1 fatal error C1190: managed targeted code requires a '/c...

help.. help(exel macro's >> mysal)
hello there i'm not a user of exel , but i have to convert an exel file (.xls) to mysql database ... trying to use alot of converters , but failed , because the exel file has macros in it ... i don't know how to convert this file with it macros to mysql ... note : the macro's jop is to update the table values every 2 seconds based on a web site page.... -------- i realy need help , and i would be thankful.. :) -- Almrshal ------------------------------------------------------------------------ Almrshal's Profile: http://www.excelforum.com/member.php?action=getinfo&...

Thumbnails: Entourage and MS Word help screens
I use OS 10.3 and Office 2004 for Mac 11.2.1 (recently downloaded the upgrade). For several weeks, Entourage messages and Microsoft Word help screens have been previewing and printing only as thumbnails. I know that Preview tends to reduce the size of documents so they will show in the small viewing window, but on my machine Entourage messages and Microsoft Word help screens preview in microscopic text--and print that way, too. The text is about 2 pts. This happens when I use any of the three printers in our office. I've noticed that while the preview PDFs are up on screen the "actu...

Help w/ SUMPRODUCT
I had great feedback when I was trying to figure out an equation for teh following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works...