Extract 1st, 2nd, and 3rd instances of text between Parentheses

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]ALL DATA[/TD]
[TD]Extracted Phone Number[/TD]
[TD]Extracted Phone Type[/TD]
[TD]Extracted Percentage[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD](555) 729-4146 (PT) (ActiveLandLine) (100%)[/TD]
[TD](555) 729-4146[/TD]
[TD]ActiveLandLine[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD](555) 729-4146 (CT) (Cell) (50%)[/TD]
[TD](555) 729-4146[/TD]
[TD]Cell[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD](555 298-0598 (ET) (LandLine) (9%)[/TD]
[TD](555 298-0598[/TD]
[TD]Landline[/TD]
[TD]9%[/TD]
[/TR]
</tbody>[/TABLE]

Link to example spreadsheet containing data on onedrive


I'm trying to extract the data into columns B, C, D from column A. Every instance in BCD is surrounded by parentheses. Any ideas on the best way to do this? The above is an example of what the data should look like if it worked properly.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi Jeff,

Try this UDF:

Code:
Function EXTRACTELEMENT(txt, N, sep)
    EXTRACTELEMENT = Split(Application.Trim(txt), sep)(N - 1)
End Function

Then the following 3 formulas across B2, C2 & D2:

B2:
=CONCAT("(",EXTRACTELEMENT(A2,2,"("))

C2:
=LEFT(EXTRACTELEMENT(A2,4,"("),LEN(EXTRACTELEMENT(A2,4,"("))-2)

D2:
=LEFT(EXTRACTELEMENT(A2,5,"("),LEN(EXTRACTELEMENT(A2,5,"("))-1)

In the example of cell A2 being "(555) 729-4146 (PT) (ActiveLandLine) (100%)"
B2: (555) 729-4146
C2: ActiveLandLine
D2: 100%
 
Upvote 0
Hi Jeff,

Try this UDF:

Code:
Function EXTRACTELEMENT(txt, N, sep)
    EXTRACTELEMENT = Split(Application.Trim(txt), sep)(N - 1)
End Function

Then the following 3 formulas across B2, C2 & D2:

B2:
=CONCAT("(",EXTRACTELEMENT(A2,2,"("))

C2:
=LEFT(EXTRACTELEMENT(A2,4,"("),LEN(EXTRACTELEMENT(A2,4,"("))-2)

D2:
=LEFT(EXTRACTELEMENT(A2,5,"("),LEN(EXTRACTELEMENT(A2,5,"("))-1)

In the example of cell A2 being "(555) 729-4146 (PT) (ActiveLandLine) (100%)"
B2: (555) 729-4146
C2: ActiveLandLine
D2: 100%

Thanks! any idea how to make it work without the code? I know it's cleaner this way but if I wanted to use it on Google sheets without a script that would be helpful.
 
Upvote 0
Thanks! any idea how to make it work without the code? I know it's cleaner this way but if I wanted to use it on Google sheets without a script that would be helpful.

Hi Jeff,

With the sample text in cell A2 try these 3 formulas across B2,C2 & D2:

B2:
=LEFT(A2,SEARCH("(",A2,2)-2)

C2:
=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))), (COLUMN(C2))*LEN($A2)+1, LEN($A2))),"(",""),")","")

D2:
=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))), (COLUMN(D2))*LEN($A2)+1, LEN($A2))),"(",""),")","")

Then copy down as appropriate. Let me know if this works!
 
Last edited:
Upvote 0
Hi Jeff,

With the sample text in cell A2 try these 3 formulas across B2,C2 & D2:

B2:
=LEFT(A2,SEARCH("(",A2,2)-2)

C2:
=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))), (COLUMN(C2))*LEN($A2)+1, LEN($A2))),"(",""),")","")

D2:
=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))), (COLUMN(D2))*LEN($A2)+1, LEN($A2))),"(",""),")","")

Then copy down as appropriate. Let me know if this works!

Thank you!
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top