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 ...

excel

HansVogelaar
Jul 17, 2023
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

HansVogelaar

MVP

Jul 17, 2023

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

SandraJee
Copper Contributor
Jul 17, 2023
THANK YOU Hans Vogelaar!!!!!!! It worked!!!!!! You've saved me from spending so much time trying to wrap my head around it!!!!

Forum Discussion

Find Text in Cell that has multiple data

Resources