Find Text in Cell that has multiple data

[SandraJee]

Hi, 

I have a data sheet that I download. The issue is that it's giving me a lot of different text/data in one cell, so I have to split it out into different cells in order to pivot and run reports on this data. I am using If(ISNUMBER(SEARCH formula to extract specific data into different cells, however, there is one particular cell where I have more than the 64 nested limit. Is anyone able to help?

Many thanks to any who is able to assist. 

[#2 · Accepted solution]

@SandraJee 

I'd do it like this:

In this example, the list in column R has just 3 items, but it can be as long as you wish - just modify the range in the formula accordingly.

The formula in I2 is

=IFERROR(INDEX($R$2:$R$4, MATCH(TRUE, ISNUMBER(SEARCH($R$2:$R$4, $H2)), 0)), "")

and this can be filled down.

Note: if you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter

[#3]

THANK YOU Hans Vogelaar!!!!!!! It worked!!!!!! You've saved me from spending so much time trying to wrap my head around it!!!!