If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Zip Code Formatting
I have a spreadsheet with data imported from a database program. One of the
columns is the zip code. Some of the zip codes have zip-plus-4 and others just have the old 5-digit zip code. I want to be able to sort by the zip code, meaning I'd end up with 5-digit zip codes interspersed amoung the zip + 4 codes. If I format the cells in the column as "special" zip + 4 formatting, it adds a five-digit field before those that don't already have the "plus 4" part. So what was 01080 becomes 00000-1080. Not acceptable. If I format the cells as text I loose the leading zeros. 01080 above becomes just 1080. Still not acceptable. Any ideas? Am I missing something? Jerry |
#2
|
|||
|
|||
Zip Code Formatting
Assuming your data is in column A, In another column (say B) put this in. =LEFT(A1,5) Now drag and fill as far as your data go.. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=557123 |
#3
|
|||
|
|||
Zip Code Formatting
That will just delete the "plus four" side of those zipcodes, but I don't
want that. I want to retain the zipcodes as they are, but be able to sort them all in proper zipcode order. So it might look like this: 01804 01804-1234 11456 12345 12345-2245 etc. 'nuther idea? Jerry "Bearacade" wrote: Assuming your data is in column A, In another column (say B) put this in. =LEFT(A1,5) Now drag and fill as far as your data go.. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=557123 |
#4
|
|||
|
|||
Zip Code Formatting
Did you try the =IF(LEN(A1)=5,A1&"-0000") This will add -0000 to cells that doesn't have +4s -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=557123 |
#5
|
|||
|
|||
Zip Code Formatting
Maybe you could use a custom format of:
[99999]00000-0000;00000 to make it look pretty. But for sorting, I think I'd use a extra column and convert the number to text. =TEXT(A1,"[99999]00000-0000;00000") Then sort by that extra column. JWCrosby wrote: I have a spreadsheet with data imported from a database program. One of the columns is the zip code. Some of the zip codes have zip-plus-4 and others just have the old 5-digit zip code. I want to be able to sort by the zip code, meaning I'd end up with 5-digit zip codes interspersed amoung the zip + 4 codes. If I format the cells in the column as "special" zip + 4 formatting, it adds a five-digit field before those that don't already have the "plus 4" part. So what was 01080 becomes 00000-1080. Not acceptable. If I format the cells as text I loose the leading zeros. 01080 above becomes just 1080. Still not acceptable. Any ideas? Am I missing something? Jerry -- Dave Peterson |
#6
|
|||
|
|||
Zip Code Formatting
I had to tweak it a bit to this:
=IF(LEN(A1)=5,A1&"-0000",A1) becasue if the existing number was already in the zip + 4 format, it would return "FALSE". However, this formula does not work on zip codes that start with a zero (mostly New England areas). 01804 doesn't get "converted" because its length is seen as less than 5. Any ideas of how to get around that quirk? Jerry "Bearacade" wrote: Did you try the =IF(LEN(A1)=5,A1&"-0000") This will add -0000 to cells that doesn't have +4s -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=557123 |
#7
|
|||
|
|||
Zip Code Formatting
You could use 2 'helper ' columns.
You could do this 2 ways. If you're going to do this often, in an on-going basis, use the Text formulas, where you can save the 2 columns of formulas, and paste your new data in the "separate me" column. If it's a one shot deal use TTC (Text To Columns), which takes some setting up each time. 1 ] Text formulas: "Separate Me" column is ColumnA. In B1 enter, =LEFT(A1,5) In C1 enter, =(LEN(A1)5)*RIGHT(A1,4) Select *both* B1 and C1 and drag down to copy as needed. Then, select A1 to Cn,and then sort by Column B, then by Column C. You should now have your data sorted as desired. Use or copy Column A as needed. You can clear ColumnA or simply overwrite it the next time you have data to import and sort. 2 ] TTC Select the column of data and make sure you have 2 empty adjoining columns, then: Data Text To Columns Fixed Width Next, Click in the "Preview Window" and place the 'Break line" *after* the dash, *before* the last 4 numbers. Then Next In the Preview Window the first column is selected by default, so just click on "Text" under "Column Data Format". Click in the second column to select it, and also change this to "Text". Then click in the Destination Box and change the default address (original data location) to the fist cell of the next empty adjoining column. This preserves the original data from being overwritten. Then Finish Now, select all 3 columns and sort on the second, then the third column. Your original data is now sorted as you wish. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JWCrosby" wrote in message ... That will just delete the "plus four" side of those zipcodes, but I don't want that. I want to retain the zipcodes as they are, but be able to sort them all in proper zipcode order. So it might look like this: 01804 01804-1234 11456 12345 12345-2245 etc. 'nuther idea? Jerry "Bearacade" wrote: Assuming your data is in column A, In another column (say B) put this in. =LEFT(A1,5) Now drag and fill as far as your data go.. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=557123 |
#8
|
|||
|
|||
Zip Code Formatting
Doesn't Bearacade's last suggestion work for you, alternatively... =IF(LEN(A1)6,TEXT(A1,"00000")&"-0000",A1) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=557123 |
#9
|
|||
|
|||
Zip Code Formatting
Hi Jerry,
Zip codes should really be text instead of numbers, to convert them in place to text with a macro retaining all digits that you have, see Fix up for 5 digit US zip codes (#fixUSzip5) http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 --- HTH, David McRitchie, My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA - Alternate ways to call code in "PowerPoint Objects"? | redhorse | Powerpoint | 7 | April 2nd, 2006 06:01 AM |
Input Masks and Zip Code Formatting | Che | New Users | 1 | March 29th, 2006 10:28 PM |
How do I get Word to retain Excel formatting, eg zip code, ssn? | soxn4n04 | Mailmerge | 1 | December 1st, 2005 04:52 AM |
Export to RTF very slow when code is present in Access report. | [email protected] | Setting Up & Running Reports | 11 | September 14th, 2004 08:17 PM |
Problem formatting ZIP code in mail merge | Peter Serratore | Setting up and Configuration | 6 | October 18th, 2003 01:50 AM |