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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,868
Messages
5,410,884
Members
403,333
Latest member
SH2020

This Week's Hot Topics

Top