Remove text outside of ( )

nyrangers1994

New Member
Joined
Aug 30, 2011
Messages
17
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try
=REPLACE(REPLACE(A1,1,FIND("(",A1),""),FIND(")",REPLACE(A1,1,FIND("(",A1),"")),LEN(A1),"")
 

nyrangers1994

New Member
Joined
Aug 30, 2011
Messages
17
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.
 

Forum statistics

Threads
1,089,228
Messages
5,406,998
Members
403,117
Latest member
redblasko

This Week's Hot Topics

Top