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

 

[kmiller7home]

I get it. I completely understand that section of the formula. That makes complete sense to me now. Thank you so much. Looking at other sections to make sure I get it. thank you again.

Kevin

 

[RoryA]

Just one additional thing: Trim also replaces multiple consecutive spaces within a string by a single space.

 

[kmiller7home]

City: =IF(LEFT(RIGHT(TRIM(A1),6),1)=" ", I get and understand the rest of this formula. Cant wrap my head around this one. I know you trim or remove all spaces from A1. I don't understand the 6 and the 1. What do those number represent? Been trying to figure this out!

KEvin

 

[RoryA]

Take the right hand 6 characters from A1 and look at the first of those. If it's a space, A1 ends with a 5 digit zip code, otherwise it must be a 9 digit one.