Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting different zip code types within a column
In Excel, with the .csv file extension, we have a column of zip codes that
are set up differently for each record. For an example, we will have 6037, 60371022 and 25463. If we highlight the whole column and select "zipcode", the 6037 becomes 06037 (like it should), the 25463 stays the same, but the 60371022 doesn't get the leading zero. If we use the zip plus 4, then the 6037 becomes 06037-0000 which is also incorrect. Is there a way to format all three types at the same time without doing too much manual manipulation? Please advise. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting different zip code types within a column
The first step is to get the data in the column in pure text format. Re-name
a copy of the .csv file .txt. This will evoke the Import Wizard. Tell the Wizard that the column is text. The next step is to enter this macto in a standard module: Sub going_postal() Dim s As String For Each r In Selection s = r.Value If Len(s) = 4 Then s = "0" & s End If If Len(s) < 6 Then r.Value = s Else If Len(s) = 8 Then r.Value = "0" & Left(s, 4) & "-" & Right(s, 4) Else r.Value = Left(s, 5) & "-" & Right(s, 4) End If End If Next End Sub The third step is to select the cells you want to convert and run the macro. If you start with: 08549 8549 85491234 085491234 the macro will convert it into: 08549 08549 08549-1234 08549-1234 If you need any help installing and running the macro, just update this post. -- Gary''s Student - gsnu200724 "prizm" wrote: In Excel, with the .csv file extension, we have a column of zip codes that are set up differently for each record. For an example, we will have 6037, 60371022 and 25463. If we highlight the whole column and select "zipcode", the 6037 becomes 06037 (like it should), the 25463 stays the same, but the 60371022 doesn't get the leading zero. If we use the zip plus 4, then the 6037 becomes 06037-0000 which is also incorrect. Is there a way to format all three types at the same time without doing too much manual manipulation? Please advise. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting different zip code types within a column
Try a custom format.
Format|Cells|Number tab|Custom category [99999]00000-0000;00000 prizm wrote: In Excel, with the .csv file extension, we have a column of zip codes that are set up differently for each record. For an example, we will have 6037, 60371022 and 25463. If we highlight the whole column and select "zipcode", the 6037 becomes 06037 (like it should), the 25463 stays the same, but the 60371022 doesn't get the leading zero. If we use the zip plus 4, then the 6037 becomes 06037-0000 which is also incorrect. Is there a way to format all three types at the same time without doing too much manual manipulation? Please advise. Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting different zip code types within a column
That works perfectly except for the cells where the zip code is, for an
example, 27656 which is a valid zip code. It's changing it to 00002-7656. How can I accommodate that? Thanks! "Dave Peterson" wrote: Try a custom format. Format|Cells|Number tab|Custom category [99999]00000-0000;00000 prizm wrote: In Excel, with the .csv file extension, we have a column of zip codes that are set up differently for each record. For an example, we will have 6037, 60371022 and 25463. If we highlight the whole column and select "zipcode", the 6037 becomes 06037 (like it should), the 25463 stays the same, but the 60371022 doesn't get the leading zero. If we use the zip plus 4, then the 6037 becomes 06037-0000 which is also incorrect. Is there a way to format all three types at the same time without doing too much manual manipulation? Please advise. Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting different zip code types within a column
Never mind! I wrote the formula wrong! Thanks! That was perfect and easy!
"Dave Peterson" wrote: Try a custom format. Format|Cells|Number tab|Custom category [99999]00000-0000;00000 prizm wrote: In Excel, with the .csv file extension, we have a column of zip codes that are set up differently for each record. For an example, we will have 6037, 60371022 and 25463. If we highlight the whole column and select "zipcode", the 6037 becomes 06037 (like it should), the 25463 stays the same, but the 60371022 doesn't get the leading zero. If we use the zip plus 4, then the 6037 becomes 06037-0000 which is also incorrect. Is there a way to format all three types at the same time without doing too much manual manipulation? Please advise. Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Mixed Column Types in One Chart | Charts and Charting in Excel | |||
Needed: Chart that combines clustered column and stacked column types | Charts and Charting in Excel | |||
Sun IF two data types are the same in a single column | Excel Discussion (Misc queries) | |||
How do I mix column chart types? | Charts and Charting in Excel |