Remove text outside of ( )

[nyrangers1994]

I have an Excel Table (ListObject) that has a column called "Location" and it's in column M. It has values such as New York Metropolitan Area (New York, NY). I need a macro that looks in this column and deletes anything that isn't within the parentheses, including the parentheses themselves, such that the columns undergo these types of transformations:

1. New York Metropolitan Area (New York, NY) --> New York, NY
2. (Boston, MA) Boston Metropolitan Area --> Boston, MA
3. Philadelphia Metropolitan (Philadelphia, PA) Area --> Philadelphia, PA

Thanks for any help anyone can provide.

 

[Jonmo1]

Try
=REPLACE(REPLACE(A1,1,FIND("(",A1),""),FIND(")",REPLACE(A1,1,FIND("(",A1),"")),LEN(A1),"")

 

[Robert Mika]

=trim(mid(a1,find("(",a1)+1,find(")",a1)-find("(",a1)-1))

 

[nyrangers1994]

Thanks for the replies, but is there a macro based approach to this?

We're extracting a daily report out of a system and to avoid paying the vendor for customizations to transform some of the data, we're attempting to create a comprehensive macro that captures all our needs where the modification to Location field is one of them. That way our operations staff can extract the daily report, run the macro against it, and everything will be formatted/transformed appropriately, without having to retype out formulas.