Removing Hyphen from Zip Codes

[Guest]

I'm making a spreadsheet to track customer's by their zip code. I have the 9 digit zip code in a column but I need to remove the hyphen. Could someone please help me with this.

Thanks.

 

[Ron de Bruin]

Try Replace

Select the cells
Edit Replace in the menubar

what: -
with: leave empty

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

I'm making a spreadsheet to track customer's by their zip code. I have the 9 digit zip code in a column but I need to remove

the hyphen. Could someone please help me with this.

 

[Bob Phillips]

Shanes,

In an adjacent cell, put this formula

=SUBSTITUTE(A1,"-","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I'm making a spreadsheet to track customer's by their zip code. I have

the 9 digit zip code in a column but I need to remove the hyphen. Could
someone please help me with this.

 

[David McRitchie]

Before you use Ron's solution select the column
and format it as text; otherwise, the substitution will
result in number instead of text and you will lose the
leading zeros for east coast zip codes and Caribbean
islands.; And this would be a preferable to creating
another a helper column with a worksheet formula.

I would recommend that you not do that though, and keep
them as text stored in the format that the post office wants to
see. Then you can do US zip codes, Canadian zip codes
or zip codes from any country. Check with your local laws
to see if it is legal to shoot such people if they are not minors.

 

[Dave Peterson]

Are you just going to wing them or what?

The applicable laws may vary based on that answer!

Before you use Ron's solution select the column
and format it as text; otherwise, the substitution will
result in number instead of text and you will lose the
leading zeros for east coast zip codes and Caribbean
islands.; And this would be a preferable to creating
another a helper column with a worksheet formula.

I would recommend that you not do that though, and keep
them as text stored in the format that the post office wants to
see. Then you can do US zip codes, Canadian zip codes
or zip codes from any country. Check with your local laws
to see if it is legal to shoot such people if they are not minors.

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

Try Replace

Select the cells
Edit Replace in the menubar

what: -
with: leave empty

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)





the hyphen. Could someone please help me with this.

 

[David McRitchie]

Guess it doesn't matter. Anyone who stores zip codes as numbers
in a database probably shoots themselves in the foot.

 

[Ron de Bruin]

and format it as text

Oops

Thanks David



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Before you use Ron's solution select the column
and format it as text; otherwise, the substitution will
result in number instead of text and you will lose the
leading zeros for east coast zip codes and Caribbean
islands.; And this would be a preferable to creating
another a helper column with a worksheet formula.

I would recommend that you not do that though, and keep
them as text stored in the format that the post office wants to
see. Then you can do US zip codes, Canadian zip codes
or zip codes from any country. Check with your local laws
to see if it is legal to shoot such people if they are not minors.

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

Try Replace

Select the cells
Edit Replace in the menubar

what: -
with: leave empty

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




remove
the hyphen. Could someone please help me with this.