Postal codes

[Karin]

Most postal codes have 5 figures. When importing files from certain external
program becomes e.g. 05235 --> 5235 (without the 0 in the beginning). How to
insert 0 in front of the 5235. I have 10000 rowes and don't want to do it
manually....

 

[Eric]

Format-cells-number-custom. Use "00000" (without quotes)

 

[Karin]

Thank you Eric! Nice weekend!

"Eric" skrev:

Format-cells-number-custom. Use "00000" (without quotes)

 

[Eric]

No problem. But remember that doing this as a formatting change means the
value remains stored as 5235, and it only looks correct so long as you limit
its use to Excel.

If you want to do something with the data (i.e. create mailing labels in
Word though a mail merge), you'll need to actually convert the *value* to
05235. Otherwise your mailing labels will read "Anytown, US 5235"

One way to convert is to sort the spreadsheet by the zips. For those
appearing as four digits, insert a helper column and a formula next to them
to add the leading zero. If, for example, 5235 appears in A1, here's the
formula to put in B1:

="0"&A1

Copy the formula down through the four-digit range. Then copy the block and
paste-special-values over the original dataset and delete the helper.

Have a great weekend.