Help to extract partial text from a cell

**doublew** · 12-12-2014, 11:19 AM

Abousetta - here are some logical statements about what I am searching for:

Multiple Sclerosis will always be spelled out in full (not abbreviated to MS)
The number I am looking for will always be contained within brackets after 'Multiple Sclerosis '
The term Multiple Sclerosis may appear anywhere in the list
Other items in the list vary from row to row

Ron - thank you, that formula does work. But I'd like to understand it better. I'm not familiar with the 10^10 notation or the {1,2,3...etc}

I've just seen other replies to my OP as well so reviewing those now

**Ron Coderre** · 12-12-2014, 11:36 AM

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.