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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
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
766
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,621
Messages
5,445,548
Members
405,337
Latest member
kann82

This Week's Hot Topics

Top