AutoFormat - help!!!

[Batfastad]

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

 

[Andy B]

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.

 

[Batfastad]

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

 

[DNF Karran]

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

 

[David McRitchie]

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