Zip Codes #3

I have a list of names and addresses in Excel.  Some of the zip codes are in 
just the regular 5 digit zip code format and some are in the zip + 4 format.  
I would like to eliminate the additional 4 digits off the zip codes that have 
them without having to manually delete those numbers from each cell.  Is this 
possible?
0
queen (5)
2/7/2005 9:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
438 Views

Similar Articles

[PageSpeed] 16

There are easier ways if the zip codes are in a separate 
column (take a look at Data, Text to Columns, using "-" as 
a delimiter), but in case your zip code is included with 
the rest of the address this formula will work provided 
the zip code is the last item in each cell.

=IF(MID(A4,LEN(A4)-4,1)="-",LEFT(A4,LEN(A4)-5),A4)
where A4 is the cell with the address.  You can add a 
blank column next to your address column.  Enter this 
formula down the column, then copy the results and paste 
as values over the original addresses.

Erin

>-----Original Message-----
>I have a list of names and addresses in Excel.  Some of 
the zip codes are in 
>just the regular 5 digit zip code format and some are in 
the zip + 4 format.  
>I would like to eliminate the additional 4 digits off the 
zip codes that have 
>them without having to manually delete those numbers from 
each cell.  Is this 
>possible?
>.
>
0
anonymous (74722)
2/7/2005 9:18:01 PM
Hi,
if you format the cells as Zipcode format, the other ones 
can be formatteed as number only format or general.

Mark

>-----Original Message-----
>I have a list of names and addresses in Excel.  Some of 
the zip codes are in 
>just the regular 5 digit zip code format and some are in 
the zip + 4 format.  
>I would like to eliminate the additional 4 digits off the 
zip codes that have 
>them without having to manually delete those numbers from 
each cell.  Is this 
>possible?
>.
>
0
anonymous (74722)
2/8/2005 4:15:11 AM
I appreciate the responses and I feel like the answer is there.  The problem 
is, I'm new to excel, self-taught and don't know how to use the answers (if 
that makes sense).  I'm trying to figure out how to use the information in 
the post from Erin Searfoss, but I'm not familiar with using delimiters.  
I've encountered that before so I've familiar with the term, just not how to 
deal with it.  Any additional advice?

"Queen" wrote:

> I have a list of names and addresses in Excel.  Some of the zip codes are in 
> just the regular 5 digit zip code format and some are in the zip + 4 format.  
> I would like to eliminate the additional 4 digits off the zip codes that have 
> them without having to manually delete those numbers from each cell.  Is this 
> possible?
0
queen (5)
2/18/2005 2:47:01 PM
Reply:

Similar Artilces:

Country Codes #2
I can't for the life of me find the GP menu item that maintains the codes. Can anyone point me to it? Open the Country Code Maintenance window. (Tools >> Setup >> Company >> Country Codes) The Country Code Maintenance window will be available only if you have marked to enable Intrastat tracking in the Company Setup Options window. See Help for more info -- Elisabeth "Ken Denman" wrote: > I can't for the life of me find the GP menu item that maintains the codes. > Can anyone point me to it? If you don't have Intrastat Tracking turned on ...

averageif with 3 crieteria
SUMPRODUCT(--(S498:S534="a"),--(G498:G534>=100),--(G498:G534<=200))/SUMPRODUCT(--(S498:S534="a"),--(G498:G534<>"")) I searched questions already answered but did not see averageif with 3 criteria I want the formula to return the average. Thanks in advance >I want the formula to return the average. The average of what? It looks like you're calculating the percentage of cells that meet a condition. Why do you think you need/want an "averageif" formula? What version of Excel are you using? -- Biff Microsoft Exc...

Can't Get CRM 3.0 Reports To Work
In the readme file that comes with server installation in the early access program it mentions under Known Issues: ************************************************ Microsoft CRM Server Privileges Required for Installing Microsoft CRM and Using an Existing Microsoft SQL Server Reporting Services Server The user installing Microsoft CRM and connecting to an existing Microsoft SQL 2000 Server Reporting Services Server must have as a minimum the Content Manager Role privileges at the Root Folder Level, and System Administrator privileges at the Site Wide Setting Level. ***********************...

Help with vlookup #3
using 2003. I am looking up a number (stored as a string) on another worksheet and returning another series of numbers (also stored as a string) - ie lookup return 411140 263791-411140E Problem - Excel formats the returned data as: 2.63791E+11. It apparently sees it as a mathmatical subtraction of an exponential number even tho it is a text. It doesn't seem to matter how I format the cell. When I format it as text, the formula does not work. (requires General to execute formula). Executing a "text to columns" does the same thing. I need the returned data to show - 263...

Im desperate to fix my fragmented memor...will advanced server save me with the 3 gb switch?
Im desperate to fix my fragmented memory, will advanced server save me with the 3 gb switch? The Situation: -Server=Windows 2000 Server sp4. -2.5 gb of RAM -Exchange=Enterprise Ed with all service packs and hotfixes. -Group Shield virus scanning Every month I recieve eventid 9582's errors, and then 12800 mail processing errors. THIS IS FREAKING KILLING ME AT WORK. Here is what I have tried. -Regedit ajustment to the heep. -ADSI ajustment to the IS (which oddly removes the errors from the event log, however MOM still detects low virtual memory errors). -sp3 (which claims to have fixed ...

CRM 3.0 Email Tracking doesn't work!!!
CRM user can send an email to contact, email title with token: cas00011000. But when the contact reply this email, the reply email can reach the server but it doesn't saved in the history(tracking doesn't work!) of CRM. The tracking function has been opened. We can't reinstall CRM server to fix this problem becasue there are many of customizations. we are having the exact same problem! did you ever get a solution?? thanks, "CEO" wrote: > CRM user can send an email to contact, email title with token: cas00011000. > But when the contact reply this email, t...

Colour code messages only to me
Hi I am running Outlook XP. There is a rule that can be set to colour code emails that are sent only to me, but what it actually does is colour code emails that only have me in the To: line of the email. If an email is sent to me and CC's in someone else, the rule treats this as a mail sent only to me. How do I set the rule so that it colour codes a message sent solely to me? Thanks in advance ...

Hidden Comments #3
If a comment is created in row 1 and then freeze panes has been applied to row 2 the comment becomes hidden behind the frozen rows. Other than adding some blank rows or making the first row deeper is there any other way of showing the comment over the top of the freeze rows line? Thanks I don't believe it is possible. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Phippsy" <Phippsy@discussions.microsoft.com> wrote in message news:3C42B096-4C61-453F-B45C-BE837D32C98D@microsoft.com... > If a comment is created...

3 Factors Analyzed
I need to create a chart that can be analyzed or visualized that has 3 dimensions of a sort. (1) I have a list of items (which can grow) (2) I have set of 5 working conditions in which each item must meet one of the five (3) but they also get analyzed for the condition daily - so I need to add a date range - and it happens twice a day (AM & PM). I haven't been able to figure out how to cleanly get all this data into a table, let alone create a chart from it. HELP! -- Bonnie M. Hi Bonnie, I think you need a clearer idea of what you want to end up with, and from there you could...

Secure code in Excel & Power Point ADD-INS
Is there any way to secure code in Excel & Power Point ADD-INS, except the password defined in VBA ? No. Use VB to create COM addins. ************ Anne Troy VBA Project Manager www.OfficeArticles.com <CLarkou@gmail.com> wrote in message news:1132296881.379001.293560@o13g2000cwo.googlegroups.com... > Is there any way to secure code in Excel & Power Point ADD-INS, except > the password defined in VBA ? > Thanks a lot, these add-ins can be called in Access also, isn't? The COM add-ins can be created by Visual Basic code or it needs C++ ? You can use VB. -- ...

color coding events
I would like to color code events on my daily calendar and wonder if there is a way to change the color of text on individual events On 3/7/2010 6:27 AM, jcurtis wrote: > I would like to color code events on my daily calendar and wonder if > there is a way to change the color of text on individual events Which version of Outlook? Outlook 2007 introduced colored categories and I use them daily for color-coding events. Prior to Outlook 2007, colored labels were your option: http://office.microsoft.com/en-us/outlook/HP030848441033.aspx Additional information: htt...

Sending POP 3 email
Once I read a POP 3 email using Winsock, how can I send an email (whose content is the POP 3 email that I just read) out in the POP 3 server using Winsock ? Thank you POP is for receiving SMTP is for sending. So either make you own SMTP control or there are some available like ostrosoft smtp "fniles" <fniles@pfmail.com> wrote in message news:elTQmQ4TLHA.4872@TK2MSFTNGP02.phx.gbl... | Once I read a POP 3 email using Winsock, how can I send an email (whose | content is the POP 3 email that I just read) out in the POP 3 server using | Winsock ? | | ...

Install 3.0 Failed
I am in the process of installing 3.0 on a server. All of the inital checks are fine. I proceed to install and it fails with an error "Action Microsoft.Crm.Setup.Server.CreateSetupUserAction failed HRESULT 0x80040237" I searched the Knowledge Base and found KB 941498. I did what it said but got the following error for msxml2.dll - LoadLibrary("msxml2.dll") failed. The specified module could not be found. How do I go about getting XML 2 (the parser I'm guessing) when it hasn't be supported in years? Is there something else I can do? I am installing 3.0...

Desktop Alert #3
Is it possible to get a new desktop alert for sub folders as you get for the main inbox? I have set a rule to create an alert but it is a permanent box on the screen and does not didapper until you click close unlike the alert for inbox. On Wed, 28 Jul 2004 06:44:41 -0700, "Drew" <anonymous@discussions.microsoft.com> wrote: >Is it possible to get a new desktop alert for sub folders >as you get for the main inbox? >I have set a rule to create an alert but it is a permanent >box on the screen and does not didapper until you click >close unlike the alert...

Office 2007 non-commercial use + Code 78F when updating
Hi All, Have a couple questions relating a Vista/Office 2007. We bought a sony vaio laptop that came pre-intalled with a trial license of office 2007. I un-installed Office completely and install a fresh copy of our partner license copy of office 2007, but when loading any office application now its shows 'Microsoft Outlook non-commercial use' and same for word,excel, etc. Anyone have any idea why this is? Second question is relating to windows updates in Vista; I get an error Code 78F when trying to install any update, and the soultions i try doesnt seem to resolve this issue....

Msgbox Code Syntax Error
I have a message box that I want to display in the OnEnter event of a List box simply to give instructions as to how the user is to enter the info. My code for the Message box isn't working and I'm getting a syntax error - I also got an error that the system is expecting an "=" in the code. I've researched it but can seem to see what I'm missing. I only want an OK button on it and then to return focus to to the control to which the code is attached. Here's the code: Private Sub lbDamagedParts_Enter() Me!Label23.Visible = True Msgbox("For th...

Column names are 1,2,3,....
I just noticed today that the column names in my excel spreadsheets are no longer alphabetical, but are numerical. The column marked "1" is defined by excel as column "1C", and row "1" is defined as row "1R". How can I get the column names to change back to the alphabet? Thanks Hi! G! Use: Tools > Options > General Remove check from "R1C1 reference style" -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good...

Question about MFC source code.
Hi, all, I find that there are some redefinitions in afxHH.h. Something looks like this: #define HID_FILE_MRU_FILE1 0x1E110 #define HID_FILE_MRU_FILE1 0x1E111 // aliases: MRU_2 - MRU_16 #define HID_FILE_MRU_FILE1 0x1E112 #define HID_FILE_MRU_FILE1 0x1E113 #define HID_FILE_MRU_FILE1 0x1E114 #define HID_FILE_MRU_FILE1 0x1E115 #define HID_FILE_MRU_FILE1 0x1E116 #define HID_FILE_MRU_FILE1 0x1E117 #define HID_FILE_MRU_FIL...

Compile Database Code Problem
I need to convert a database to ACCDE and need to compile my VBA, however when I select Compile under the Debug menu I get the following error, "Method or data member not found". The problem is that the Sub it is identifing is a part of my database and works well when I use the database. The code that failed was the following with ".Combo2" highlighted in the second line. Private Sub Combo2_AfterUpdate() TransferSection = Me.Combo2 MsgBox "Transfer Section is " & TransferSection RunCommand acCmdRecordsGoToNew RunCommand acCmdSelectRecord...

Code versus Int
Hi, Web applications often use a parameter to select a specific record. Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to retriev a specific mail message. Question 1) Is an Int type not prefered? Question 2) What is a smart way to generate unique random numbers? Date + number??? 051320101 = 05-13-2010-1 Thanks! Arjen Arjen (boah123@hotmail.com) writes: > Web applications often use a parameter to select a specific record. > Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to > retriev a specific...

Zip Codes 5 digits only
Is there a way to change zip codes in a spreadsheet to only display 5 digits? Hi, Use custom cell format 00000. This will add leading zeros, if the cod is shorter than 5 digits. - Asse -- Message posted from http://www.ExcelForum.com =LEFT(zipcode,5) will strip off the extended codes. Jerry anonymous@discussions.microsoft.com wrote: > Is there a way to change zip codes in a spreadsheet to > only display 5 digits? ...

How to customize error bars by VB code?
Hello NG, from a Visual Basic (6) project, I need to customize the error bar of a chart, to get a different value for each data point. In Excel, this is done with a range reference: ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=Hoja1!R1C3:R2C3" but I need to do this by VB code, in order to control a OLE-Chart. Reference to the datasheet-range is not working: oGraph.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _ Type:=xlCustom, Amount:=oGraph.Application.DataSheet.Range("A1:A3") Any ide...

code list of UTF-8
i searching for a list of the double character encoding spezial charcters in UTF-8 such as äüö... Can any one send me link where i can find a complete list? -- best regards Rolf Brockmann Rolf Brockmann wrote: > i searching for a list of the double character encoding spezial > charcters in UTF-8 such as äüö... > Can any one send me link where i can find a complete list? See http://www.faqs.org/rfcs/rfc2279.html. Cheers, -- http://www.joergjooss.de mailto:news-reply@joergjooss.de Rolf Brockmann wrote: > i searching for a list of the double ch...

Access 2003 VB Code Export Query Failing with Access 2007 user
I have a set of excel export queries I created in an access 2003 DB. Now, some of my users have upgraded to Access 2007 and are running in to a "Parameter" error when the execute the "canned" excel export queries I created for them. My only presumption is that it is due to the fact that I have hardcoded the export function, to go to an Office 2003 Excel file, and for whatever reason, Access 2007 cannot handle this. This make sense? And if so, any suggestions? I suppose if there was an easy way to test what version of office/excel they are running, I could change...

Free business opportunity #3
If you need to make 2,000 - 5,000 per week - you need to check this out. No cost or obligation full details. www.profitsdaily.biz --- MAF Anti-Spam ID: 20041209205203P0d4HhB4 ...