SOLVED

# Find Text in Cell that has multiple data

Copper Contributor

# Find Text in Cell that has multiple data

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 Replies
best response confirmed by SandraJee (Copper Contributor)
Solution

# Re: Find Text in Cell that has multiple data

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

# Re: Find Text in Cell that has multiple data

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

Accepted Solutions
best response confirmed by SandraJee (Copper Contributor)
Solution

# Re: Find Text in Cell that has multiple data

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