Using Substitute/Replace to Change Cell Formats For Multiple States

[jclegg42002]

Hi, I try not to bug anyone on here but I am trying to think of how to accomplish the following:

I have a spreadsheet that has a hub name, for example; TAYLOR (MI), and I need it to read out TAYLOR.MI - I can accomplish this no problem. However, I have states like (KY) and (IN). (I cannot download XL2BB on my work computer.)

Hub	HUB2	HUB3
TAYLOR (MI)	TAYLOR.MI	TAYLOR.MI
NEW HAVEN (MI)	NEW HAVEN.MI	NEWHAVEN.MI
NEW HAVEN (MI)	NEW HAVEN.MI	NEWHAVEN.MI
GARDEN CITY (MI)	GARDEN CITY.MI	GARDENCITY.MI
ROCHESTER HILLS (MI)	ROCHESTER HILLS.MI	ROCHESTERHILLS.MI
ELIZABETHTOWN (KY)	ELIZABETHTOWN (KY)	ELIZABETHTOWN(KY)
SPEEDWAY (IN)	SPEEDWAY (IN)	SPEEDWAY(IN)

How do I get HUB to change in column HUB2 to taylor.mi, new haven (MI) --> newhaven.mi, elizabethtown (KY) --> elizabethtown.ky ---- all in one formula?

I used substitute in HUB2 [ =SUBSTITUTE(E2," (MI)",".MI") ] - and REPLACE in HUB3 [ =SUBSTITUTE(F3," ","") ]

I know not being able to use the XL2BB addon makes this difficult and I apologize. If anyone is confused by what I am asking please ask so I can better explain... (I assume there needs to be multiple IF statements included?)

Thank you for all of your help!

MS Office 365 (enterprise)

 

[mikerickson]

How about =SUBSTITUTE(SUBSTITUTE(A1," (","."),")","")

 

[jclegg42002]

How about =SUBSTITUTE(SUBSTITUTE(A1," (","."),")","")

Well... that did work, I feel realllllly stupid right now.

Thank you!