Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
international postal codes | Excel Worksheet Functions | |||
formula for Canadian postal codes | Excel Worksheet Functions | |||
formula for Canadian Postal Codes | Excel Worksheet Functions | |||
UK Postal codes in Excel | Excel Worksheet Functions | |||
Distances between Postal Codes | Excel Discussion (Misc queries) |