Format

[Guest]

I have a spreadsheet with a list of Zip Codes. The Zip Codes are formatted
as
"#####-####". So it looks like 98507-9046 but was entered as 985079046.

I want the Zip Codes to be stored as 98507-9046 instead of 985079046. How
can I do this for over 1000 entries instead of doing it manually one by one?

thanks, Lamar

 

[Peo Sjoblom]

Use a help column, assume the data starts in A2, insert a new column B
(unless B is already empty) and in B2 put

Copy down the formula 1000 rows (double click lower right corner of B2)

copy and paste special as values in place, once that is done you can delete
column A

 

[Pete_UK]

These formulae can convert the numerics to the text format you
require:

=TEXT(A1,"00000-0000")

or

=LEFT(A1,5)&"-"&RIGHT(A1,4)

assuming the data starts in A1. Just copy them down the column, then
you can fix the values and get rid of the original column.

Hope this helps.

Pete

 

[Peo Sjoblom]

Oops! Forgot to include the formula

=TEXT(A2,"00000-0000")

 

[Guest]

custom format:

00000-0000