Removing the Comma in the city,state zip in a single cell

[kmiller7home]

I hope you can revive my faith in Excel Forums. I'm new to excel and I know this is a simple equation. How do I remove all comma's from the city, state zip cell in excel? There are some that don't have the comma but many do. There are also some spaces in there as well that need removed. Is there one formula that can remove all comma's and extra spaces? In the example below you see a comma and then below an extra space between ALLEN and KY. Hope you all in your INFINITE wisdom can help.

I'm using Win7Pro and Excel 2013

ALGER, OH 45812
ALLEN KY 41601
ALLEN KY 41601
ALLEN KY 41601

Thank You

Kevin

 

[RoryA]

Hi, and welcome to the forum.

=TRIM(SUBSTITUTE(A1,",",""))
and copy down.

 

[Joe4]

Welcome to the Board!

You an highlight all your data and use Find/Replace to find all commas and replace with nothing to get rid of your commas.
Likewise, if you have extra spaces, you can use Find/Replace to replace all instances of two consecutive spaces with a single space.

 

[kmiller7home]

Thank you very much! That worked great. Two more questions for you. I need to seperate the city, state and zip in different columns. Which is no biggie until I get to a city with two words (example below) is there a good formula to use in this case?

HAZARD, KY. 41702
HAZARD, KY 41702
HI HAT KY 41636
HI HAT KY 41636
HI HAT KY 41636

Also, if I understand the function substitute according to that formula, it looks at cell A1 and if it sees a , it removes it and trim removes all extra spaces. right?

Trying to learn as much as possible!!
Win7 Pro using Excel 2013

Kevin

 

[Joe4]

ADVERTISEMENT

If you have too many different variations to contend with, this can get pretty messy awfully fast! So you want to clean-up and standardize your data as much as possible first.

So the first thing you want to do is clean-up your data so that it is all in the same format. You mentioned cleaning up the commas and extra spaces. Do the same thing with periods too.

If you can do that, and assuming that your Zip Codes are always 5 characters, you could use these formulas:
City: =LEFT(TRIM(A1),LEN(TRIM(A1))-9)
State: =LEFT(RIGHT(TRIM(A1),8),2)
Zip: =RIGHT(TRIM(A1),5)
(for an entry in cell A1)

 

[kmiller7home]

Thank you. All of them worked perfectly except for the rows with 9 digit zip codes. Out of the 3,000 plus rows only about 100 of them had the 9 digit which I can manually go thru and fix. Not a big deal at all!!
I've read other blogs concerning the left and right functionality and the numbers at the end of the formulas. I've not been able to understand the blogs. Would you have time to briefly explain the usage. IF not I understand! If I can understand the concept of them especaily the numbers at the end, then I WILL remember and NEVER ask again! You've been so helpful!!! Thank you SO much.

 

[Joe4]

ADVERTISEMENT

Would you have time to briefly explain the usage.

OK. Let's just mention what a few basic functions do:

TRIM(string) - simply drops any blank spaces at the end of your string
i.e. TRIM("Hello there ") would return "Hello there" (without the quotes)

TRIM(string) - simply returns the length of a string
i.e. LEN("Computer") would return 8

Regarding the RIGHT, LEFT, and MID functions, here is a good link on each one. I find the examples most helpful.
LEFT: MS Excel: LEFT Function (WS, VBA)
RIGHT: MS Excel: RIGHT Function (WS, VBA)
MID: MS Excel: MID Function (WS, VBA)

So let's walk through the Zip Code calculations:
Zip: =RIGHT(TRIM(A1),5)

Just like back in Math class, you want to work from the inside out, starting with the inner-most parentheses.
So TRIM(A1) simply takes whatever is in cell A1, and drops any extra spaces at the end.
So if we have "HAZARD KY 41702" in A1, it will return "HAZARD KY 41702"

Now, we are just applying the RIGHT function on that: RIGHT(...,5)
So that is telling us to return the last 5 characters from our string.
If we have "HAZARD KY 41702", it will return "41702".

Make sense?

You would apply the same sort of logic to the other two. Try to see if you can follow the same process I explained above, and see if you can follow the logic. If not, please feel free to post any specific questions back here, and we'll help clarify it for you.

 

[Joe4]

Thank you. All of them worked perfectly except for the rows with 9 digit zip codes. Out of the 3,000 plus rows only about 100 of them had the 9 digit which I can manually go thru and fix.

If all of your zip codes are either exactly 5 characters, or exactly 9 characters, we can add an IF statement to each one, checking the 6 character from the end. If it is a space (which it would be for 5 digit zip codes, as the character before the zip codes starts is a space in that instance, we can use our original formula "as-is". Otherwise, we need to move over 4 characters (to account for the extra 4 characters in the Zip Code).

So, we can modify our original formulas so that they will, regardless of whether or not the Zip Code is 5 or 9 digits:
City: =IF(LEFT(RIGHT(TRIM(A1),6),1)=" ",LEFT(TRIM(A1),LEN(TRIM(A1))-9),LEFT(TRIM(A1),LEN(TRIM(A1))-13))
State: =IF(LEFT(RIGHT(TRIM(A1),6),1)=" ",LEFT(RIGHT(TRIM(A1),8),2),LEFT(RIGHT(TRIM(A1),12),2))
Zip: =IF(LEFT(RIGHT(TRIM(A1),6),1)=" ",RIGHT(TRIM(A1),5),RIGHT(TRIM(A1),9))

You can see how these start to look messy looking pretty fast, but it is actually not as complex as it looks. The key is just understanding the three clauses of an IF formula: MS Excel: IF Function (WS)

So, for the Zip,
our condition is: LEFT(RIGHT(TRIM(A1),6),1)=" "
value if true: RIGHT(TRIM(A1),5)
value if false: RIGHT(TRIM(A1),9)

So you can see out "value if true" is just the original formula we had posted before, and the "value if false" is really just the same thing, with 4 more characters (to account for the 9 digit zip code).

 

[kmiller7home]

Those formula's worked perfectly. I had to go and remove the - for the 9 digit zip and then all columns formated correctly, awesome! I still am cloudy on the functionality I'll type out whats in my head for the City formula... trim A1 (removes spaces) then look at the last 6 digits from the right then if the left (one) digit equals a space then trim A1 left ... not sure on the LEN -9 does or mean. If no space then ... ,LEFT(TRIM(A1),LEN(TRIM(A1))-13)) here Trim A1 left then not sure on the LEN(trim(A1))-13 does. that is hangin me up. I'm trying to write this forumula in english. I do believe I'm getting closer.

 

[Joe4]

Let's take a look at the original City calculation:
City: =LEFT(TRIM(A1),LEN(TRIM(A1))-9)

If all of our entries end with this format:
- single space
- 2 character state code
- single space
- 5 digit zip code
then we know all we have to do is to remove the last 9 characters of each entry to get the City.

How can we do that?
First, how can we get the total length of the entry in cell A1? We used the LEN (length) function.
So, if we start at the beginning, and take everything except for the last 9 characters in A1, we will get just the city.
So, we use the LEFT function, which starts at the beginning, and we tell it to take all but the last 9 characters of A1.

Does that help clarify things?