Postal codes
 

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

Postal codes
 

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

"Karin" wrote:

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

Postal codes
 

Thank you Eric! Nice weekend!

"Eric" skrev:

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

"Karin" wrote:

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

Postal codes
 

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.

"Karin" wrote:

Thank you Eric! Nice weekend!

"Eric" skrev:

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

"Karin" wrote:

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

Postal codes
 

.... or merely use =TEXT(A1,"00000") throughout?
--
David Biddulph

"Eric" wrote in message
...
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.

"Karin" wrote:

Thank you Eric! Nice weekend!

"Eric" skrev:

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

"Karin" wrote:

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