Quick way to show only numeric in a cell?

**Joe@WSC** · 07-08-2009, 08:53 AM

I am almost done with the school thing I've been working on and I just need to clean up one more thing. The zip code column can be a little sloppy sometimes. Many of them are perfect, but many carry a little too much over from where I am pulling it, so the states name is in there. Is there anyway I can filter it or something without losing any of my rows so that it only shows the numeric stuff in that column.
Example:
SD, 57401

Need:

57401

Also, some have hyphens like 1324-56789. So I don't know how to say only numeric but include hyphenated numbers either.

Thanks for all the help.

**NBVC** · 07-08-2009, 09:08 AM

So if it is hyphenated, you do want the whole hyphenated number?

**Joe@WSC** · 07-08-2009, 09:12 AM

Yeah it needs to show the zip code, whether it is the 5 digit or the 4-5 digit style.....actually, I'd probably be able to get away with just the 5 digit code. So could we just say only show 5 characters from the right or something like that?

**Joe@WSC** · 07-08-2009, 09:15 AM

Never mind, the zip code is 5-4 not 4-5 so that idea I had wouldn't work. I did find something to filter out the SD's though, there is a does not contain thing under filter so that worked. Now I just need to figure out how to deal with the longer zip codes getting cut off.

**NBVC** · 07-08-2009, 09:19 AM

Would something like this work...

In a new column, add formula:

=IF(ISNUMBER(SUBSTITUTE(A1,"-","")+0),A1,RIGHT(A1,5))

where A1 is 1st cell to purge...

copy formula down.

To replace originals, copy the new column and Edit|Paste Special >> Values over the old.. then delete the new.

**Joe@WSC** · 07-08-2009, 09:35 AM

Sorry, didn't really work. I don't know if I am explaining it very well and I don't think I am allowed to post an example because of what I am working on. I am pulling the zip code out of another column that has city state and zip. That is why the zip code column has some extra info sometimes. The original problem was that it doesn't move over as clean as the city column I extracted from the same original column. Sometimes it cuts the zip off, sometimes it moves the state over with it. All I really want now is the 5 digit normal zip code to move over, but it is hard because not all zip codes were formatted the same way. Thanks for your help so far and if you have any more ideas I am grateful for them.

**NBVC** · 07-08-2009, 09:39 AM

You would need to post a sample sheet using "fake data" that is organized/formatted the same way...so that we get an idea of what you are up against....

and show what you want the outcome to look like.

**Joe@WSC** · 07-08-2009, 09:47 AM

Ok, I will whip something up quick.

**Joe@WSC** · 07-08-2009, 10:18 AM

It says I don't have am missing a security token when I try to add the attachment. It is saved as an xlsx which is an ok file type right?

**NBVC** · 07-08-2009, 10:22 AM

It should be ok as long as it is not over 1000kb in size.. or you can try zipping it...

as far as the security is concerned.. not sure what that means...

try again, using the paperclip icon, browse, upload..... if it still gives trouble I will report to Admin.

**DonkeyOte** · 07-08-2009, 10:30 AM

If the ZIP is always at the end of the string then perhaps:

=RIGHT(" "&A1,5+(5*(MID(" "&A1,LEN(" "&A1)-4,1)="-")))

**Joe@WSC** · 07-08-2009, 10:44 AM

Sorry, I don't know how to delete messages, but this one should be deleted.

**Joe@WSC** · 07-08-2009, 10:48 AM

Ok, here we go. A B and C are input. The rest is the clean and separated output we want, but as you can see the zip column is not so clean. I filtered the extra rows out to hide the mess, but they are there for referencing. In the end I want to move the outputs to a separate file so I can export that to a program.

**DonkeyOte** · 07-08-2009, 11:01 AM

I'm sorry to say I can't really make sense of the file... if we insert new columns between C & D so that you have a new blank column in D... then

D1: =IF(ISERR(--RIGHT(B1,4)),"",RIGHT(" "&B1,5+(5*(MID(" "&B1,LEN(" "&B1)-4,1)="-"))))
copied down

should generate

54321 in row 6
67890-1234 in row 8
67854 in row 10
34567 in row 12

all other rows should return blank given there is seemingly no zip code to retrieve.

**Joe@WSC** · 07-08-2009, 11:07 AM

Sorry if it was confusing. But would your formula work on a file that is thousands of schools long. Or would I have to go through and change each one to match the zip codes.

**DonkeyOte** · 07-08-2009, 11:10 AM

I'm afraid I don't understand the question ...

The formula provided would pull a Zip if it appears at the end of the string and follows one of 2 patterns:

##### or #####-####

**Joe@WSC** · 07-08-2009, 11:34 AM

Sorry, I just didn't follow along with the formula, but it seems to be working the way I want it now. Thanks for all the help and have a good day.

**Joe@WSC** · 07-08-2009, 11:49 AM

Actually, that formula is amazing. It works perfectly. You are an excel genius.

Quick way to show only numeric in a cell?

LinkBack

Thread Tools

Rate This Thread

Display

Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Re: Quick way to show only numeric in a cell?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions