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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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,223
Messages
5,443,183
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top