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>
1ABCD
2ALL DATAExtracted Phone NumberExtracted Phone TypeExtracted Percentage
3(555) 729-4146 (PT) (ActiveLandLine) (100%)(555) 729-4146ActiveLandLine100%
4(555) 729-4146 (CT) (Cell) (50%)(555) 729-4146Cell50%
5(555 298-0598 (ET) (LandLine) (9%)(555 298-0598Landline9%

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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
723
Office Version
365
Platform
Windows
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%
 

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
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.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
723
Office Version
365
Platform
Windows
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:

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
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!
 

Forum statistics

Threads
1,089,228
Messages
5,407,005
Members
403,117
Latest member
redblasko

This Week's Hot Topics

Top