CODEWITHSUNDEEP
excelexcel-formulaexcel-2010formula
Dstumptown
Dstumptown

Reputation: 3

Excel - extracting text between parentheses after finding certain text

I have tried searching for an answer but can't seem to find the exact solution. I am trying to extract text between two parentheses starting at a certain string of text. I.e. the cell contains the following – ABC (12.3%) DEFGH (18.1%) IJKL (17.2%). I want to have a reference cell saying "ABC" then the cell below return the % number in between parentheses following ABC.

The current formula I am using is the below, where G6 is the full line of text and I5 is the reference cell "ABC": =MID(G6,FIND(I5,G6)+FIND("(",G6),FIND("(",G6)+1+FIND(")",G6)-FIND("(",G6)-7)

This will work when the Input is 3 characters long (ABC), but won't work when the string text is a different length.

Can someone help me create a formula where I can pull the % number regardless of how many characters there are?

Upvotes: 0

Views: 1825

Answers (2)

Seabhach
Seabhach

Reputation: 41

If you are trying to separate all the different strings, then it might be best to split it up.

String Splitting

Cell A1: 'The current String'
Cell B1:    =SEARCH("(",A2)
Cell C1:    =SEARCH(")",A2)
Cell D1:    =MID($A$2,1,B2-1)
Cell E1:    =MID($A$2,B2+1,(C2-B2)-1)
Cell F1:    =SEARCH("(",A2,12)
Cell G1:    =SEARCH(")",A2,12)
Cell H1:    =MID($A$2,C2+1,(F2-C2)-1)
Cell I1:    =MID($A$2,F2+1,(G2-F2)-1)
Cell J1:    =SEARCH("(",A2,26)
Cell K1:    =SEARCH(")",A2,26)
Cell L1:    =MID($A$2,G2+1,(J2-G2)-1)
Cell M1:    =MID($A$2,J2+1,(K2-J2)-1)

  • B1 & C1 will search for the first appearance of "(" & ")".

  • D1 & E1 will then use those numbers in B1 & C1 to find the text you're searching for.

  • F1 & G1 will search for the second appearance of "(" & ")"

  • H1 & I1 will then use those numbers in F1 & G1 to find the text you're searching for.

  • J1 & K1 will search for the third appearance of "(" & ")"

  • L1 & M1 will then use those numbers in J1 & K1 to find the text you're searching for.

This process breaks it down for every piece of string. In the picture I attached I also added additional fields for trimming the results to eliminate any blank spaces.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

With the big string in A1 and ABC in B1, try:

=LEFT(MID(A1,FIND(B1,A1)+LEN(B1)+2,9999),FIND(")",MID(A1,FIND(B1,A1)+LEN(B1)+2,9999))-1)

enter image description here

what is going on:

The core of the formula: MID(D1,FIND(E1,D1)+LEN(E1)+2,9999) discards the front end of the string and returns:12.3%) DEFGH (18.1%) IJKL (17.2%).

The enclosing part discards the closing parens and every that follows.

Upvotes: 1

Related Questions