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

[jeffcoleky]

<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>

1	A	B	C	D
2	ALL DATA	Extracted Phone Number	Extracted Phone Type	Extracted Percentage
3	(555) 729-4146 (PT) (ActiveLandLine) (100%)	(555) 729-4146	ActiveLandLine	100%
4	(555) 729-4146 (CT) (Cell) (50%)	(555) 729-4146	Cell	50%
5	(555 298-0598 (ET) (LandLine) (9%)	(555 298-0598	Landline	9%

<tbody>
</tbody>

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.

 

[tyija1995]

Hi Jeff,

Try this UDF:

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%

 

[jeffcoleky]

Hi Jeff,

Try this UDF:

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.

 

[tyija1995]

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!

 

[jeffcoleky]

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!