Help to extract partial text from a cell

Hello and welcome to the forum,

As a general rule of thumb, you have to be able to set 'rules' to guide any formula or macro to run; they are not inherently intuitive. You can always set multiple rules in a hierarchical fashion so that for example:

1. the number comes between parentheses right after the term "Multiple Sclerosis"... if not then...
2. the number comes right after "Multiple Sclerosis"... if not then...
3. the number comes right after "MS"... etc.

If you can't dictate a logical sequence because the data will be completely random then the results will also be completely random.

Having said that, can you provide us with logical If then statements with before/ after examples.

Thanks.

abousetta

Using your posted workbook, this regular formula pulls the count for the I2 value

Please Login or Register  to view this content.

Is that something you can work with?

Hi Ron,

I think the OP is looking for only the number after a specific disease, but the way it's presented can different from cell to cell.

Maybe this

=LOOKUP(2^15,-LEFT(MID(C2,SEARCH("Multiple Sclerosis ",C2)+LEN("Multiple Sclerosis "),10),{1,2,3,4,5,6,7,8,9,0}))

Or

=SUBSTITUTE(MID(C2,SEARCH("Multiple Sclerosis ",C2)+LEN("Multiple Sclerosis ("),3),")","")

Perhaps I didn't explain the formula I posted correctly...
It finds the search text and returns the number in parentheses after that text.

Using your posted workbook, this regular formula pulls the count for the I2 value
EDITED TO CLARIFY:
This formula finds the search text (in cell I1) in the Disease Areas column and returns the number in parentheses after that text. If the search text does not exist in the referenced cell, the formula returns 0.

Please Login or Register  to view this content.

Is that something you can work with?

In the formula I posted: =IFERROR(LOOKUP(10^10,--LEFT(MID(C2,SEARCH($I$1,[@[Disease Areas]])+LEN($I$1),10),{1,2,3,4,5,6,7,8})),0)

This section: LOOKUP(10^10,--LEFT(MID(C2,SEARCH($I$1,[@[Disease Areas]])+LEN($I$1),10),{1,2,3,4,5,6,7,8}))
takes advantage of a LOOKUP feature(?).

Regarding an array of numbers,
1) LOOKUP ignores any error values (#N/A!, #VALUE!, etc)
2) If the lookup value is greater than the all of the arrayed numbers...it returns the last numeric value

using Row_2 as an example
Once this section: MID(C2,SEARCH($I$1,[@[Disease Areas]])+LEN($I$1),10)
returns: "5)Stroke "

This part: LEFT(MID(C2,SEARCH($I$1,[@[Disease Areas]])+LEN($I$1),10),{1,2,3,4,5,6,7,8})
builds an array of the left 1, then 2, then 3, etc. characters and
returns this array: {"5","5)","5)","5)S","5)St","5)Str","5)Stro","5)Strok"}

and this part tries to convert each element of that array to a number: --LEFT(MID(C2,SEARCH($I$1,[@[Disease Areas]])+LEN($I$1),10),{1,2,3,4,5,6,7,8})
resulting in this array: {5,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

Finally, when the LOOKUP function tries to match 10^10...which is larger than any value you would expect to find...it matches on the last numeric value: 5

If the Multiple Sclerosis text was this: Multiple Sclerosis (500)
The resulting array would be this: {5,50,500,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
so the LOOKUP match would be 500

I hope that helps.

All that matters is that you get something that works that you feel comfortable using. I'm glad you got that.