Removing the +4 from zip+4

[Guest]

Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I need
to remove the last 5 characters, leaving only the basic 5 digit zip-code.
The column contains about 10,000 entries. Please suggest methods by which
this can be done. Thanks!

 

[Dave Peterson]

Are your values in that column Text or are they numbers formatted to show the
dash?

If they're text
select the column
data|text to columns
delimited by -
skip (do not import that second column)
Put it right back in the same location.
format|cells|number tab
custom
00000
(to keep leading 0's showing)

Or use a helper column with formulas like:

=left(a1,5)

======
If they're really numbers formatted to look that way:

I'd use a helper column
=INT(A1/10000)

And format that nicely.

 

[William]

Manu

Assuming the column in question is Column A, enter in cell B1 =Left(A1,5)
and copy cell B1 down column B.
Then format Column B as text.
Then Copy/PasteValues Column B

--
XL2002
Regards

William

(e-mail address removed)

| Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I
need
| to remove the last 5 characters, leaving only the basic 5 digit zip-code.
| The column contains about 10,000 entries. Please suggest methods by which
| this can be done. Thanks!

 

[Don Guillett]

try this
Sub trimcode()
For Each cel In Selection
cel.NumberFormat = "@"
cel.Value = Left(cel, 5)
Next cel
End Sub

 

[David McRitchie]

How NOT to make friends with the US Post Office..

You would probably want to format the column as text
before removing the last five characters.

If this is just for consistency you could left justify everything,
but it is best to use text for all zipcodes for all countries then
you avoid a lot of problems and you zipcodes both 5 digit and
zip+4 codes would automatically line up as left justified since
they would all be text upon entry (or reentry).

I guess I'll find out in a few weeks whether a post card to
a Canadian address without zip code and without the number portion
of the street address will work there -- not a very big street.