CODEWITHSUNDEEP
excel
spike
spike

Reputation: 10004

Check multiple chunks of string against a range in excel in one go

I have a range of "Good" values like this:

Apple
Banana
Pear
Grape
Melon

And a range of input data like this ( I could probably hardcode a max of 10 ; separated elements if I had to):

Apple; Grape  //Good
Watermellon   //bad
Pear; Peach   //bad, ideally return "Peach"

I'm trying to come up with a function to check each cell of the input data against the acceptable values. So it should take "Watermelon" or "Pear;Peach" and return FALSE (ideally it would give me the strings that caused FALSE), and TRUE for "Apple"

VBA is not acceptable.

I know how to check an individual string like "Apple", but not how to check multiple substrings at once. I tried a mess of FIND + MID lookups, but felt like there must be a better way. Is there?

Upvotes: 1

Views: 160

Answers (1)

assylias
assylias

Reputation: 328669

Without vba, it will be messy. One way if you can accept manual steps is to use the text to columns feature in excel, using ; as a delimiter.

If you know that you can't have more than 10 items, you could have 10 columns with one item or blank in each using text o columns or a combination of find, mid. It would be a little less messy and would allow you to spot which colum contains an invalid value with vlookups or conditional formatting for example.

Upvotes: 1

Related Questions