Forum Discussion
Solved
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.
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
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
- THANK YOU Hans Vogelaar!!!!!!! It worked!!!!!! You've saved me from spending so much time trying to wrap my head around it!!!!
