Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing the +4 from zip+4
Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I need
to remove the last 5 characters, leaving only the basic 5 digit zip-code. The column contains about 10,000 entries. Please suggest methods by which this can be done. Thanks! |
#2
|
|||
|
|||
Are your values in that column Text or are they numbers formatted to show the
dash? If they're text select the column data|text to columns delimited by - skip (do not import that second column) Put it right back in the same location. format|cells|number tab custom 00000 (to keep leading 0's showing) Or use a helper column with formulas like: =left(a1,5) ====== If they're really numbers formatted to look that way: I'd use a helper column =INT(A1/10000) And format that nicely. Manu wrote: Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I need to remove the last 5 characters, leaving only the basic 5 digit zip-code. The column contains about 10,000 entries. Please suggest methods by which this can be done. Thanks! -- Dave Peterson |
#3
|
|||
|
|||
Manu
Assuming the column in question is Column A, enter in cell B1 =Left(A1,5) and copy cell B1 down column B. Then format Column B as text. Then Copy/PasteValues Column B -- XL2002 Regards William "Manu" wrote in message ... | Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I need | to remove the last 5 characters, leaving only the basic 5 digit zip-code. | The column contains about 10,000 entries. Please suggest methods by which | this can be done. Thanks! |
#4
|
|||
|
|||
try this
Sub trimcode() For Each cel In Selection cel.NumberFormat = "@" cel.Value = Left(cel, 5) Next cel End Sub -- Don Guillett SalesAid Software "Manu" wrote in message ... Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I need to remove the last 5 characters, leaving only the basic 5 digit zip-code. The column contains about 10,000 entries. Please suggest methods by which this can be done. Thanks! |
#5
|
|||
|
|||
How NOT to make friends with the US Post Office..
You would probably want to format the column as text before removing the last five characters. If this is just for consistency you could left justify everything, but it is best to use text for all zipcodes for all countries then you avoid a lot of problems and you zipcodes both 5 digit and zip+4 codes would automatically line up as left justified since they would all be text upon entry (or reentry). I guess I'll find out in a few weeks whether a post card to a Canadian address without zip code and without the number portion of the street address will work there -- not a very big street. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Manu" wrote in message ... Column contains 10 characters (e.g. 02453-1234) making up the zip+4. I need to remove the last 5 characters, leaving only the basic 5 digit zip-code. The column contains about 10,000 entries. Please suggest methods by which this can be done. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing duplicate records in excel, how to do it? | Excel Discussion (Misc queries) | |||
Removing ' character from cells | Excel Discussion (Misc queries) | |||
Removing a drop down list from a cell | Excel Discussion (Misc queries) | |||
Removing Hidden But Keep Values | Excel Discussion (Misc queries) | |||
Removing links to other worksheets from within a workbook | Excel Discussion (Misc queries) |