How to extract a string from 2 strings in Excel counting from the second string instead from the default left side

Question

Here's a string:

Sample text here, EXTRACTTHIS(), and ignore the rest.

I want EXTRACTTHIS() to be extracted, so I used this simple formula:

=MID(LEFT(A5,FIND("()",A5)+1),FIND(" ",A5)+1,LEN(A5))

However I got this: text here, EXTRACTTHIS()

Of course I can just mod it to be =MID(LEFT(A6,FIND("()",A6)+1),FIND(" ",A6)+10,LEN(A6)) to get EXTRACTTHIS().

But I want this formula to work with the whole column such as the following example:

I give you the next sample: WHAT_IF_THE_STRING_LENGTH_IS_DIFFERENT(), what to do?

The problem is that Excel counts from the left side of the parent string. I want Excel to count the 1st substring ' ' from the right side of the 2nd substring which is (). Is it doable?

Answer 1

In your examples, it appears you want to extract the substring that ends with ().

If that is not what you mean, please be more specific.

Try: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("()",A1)+1)," ",REPT(" ",99)),99))

• Find the location of the (): FIND("()",A1)
• Extract the portion of the string up to and including the ()
LEFT(A1, ... +1) => Sample text here, EXTRACTTHIS()
• Then extract the last space separated substring from that
  TRIM(RIGHT(SUBSTITUTE(... , " ",REPT(" ",99)),99))