Formula to find multiple texts with in a string

[MZING81]

Hi Everyone,

I'm working on a formula to find multiple occurences of different text strings within a single cell. The problem is with one formula will find one text string, but I dont know how to tell to find the others. I have another one that can find either or, but wont return a blank cell if it cant find anything

=IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

=IF(SUM(COUNTIF(G67,{"*BLAH-2*","*GOOD-3*"}))>0,"BLAH-2","GOOD-3")

Any assistance will greatly be appreciated

MZING81

[Ken Puls]

Can you post a small sample workbook of the input and output that you'd expect to see? Just a few rows of different data so that we can see how the formula needs to work?

[Canapone]

Hi All,

an attempt using an array formula to be confirmed with control+shift+enter.

In the example F2:F50 hosts the list of substrings you'd need to check, A2 hosts the first big string.

=IFERROR(INDEX($F$2:$F$50,MATCH(1,SEARCH("*"&$F$2:$F$50&"*",A2),0)),"")

Hope it's a little help

[MZING81]

The strings are about 75 charcters of data with most of the info not being that necessary, I just want to pull out the necessary portions. There are about 14 different text strings that I want to extract. I've been using text to columns, but that has been creating issues with sumproduct formulas that will not account for the data, even though its there.

Can you post a small sample workbook of the input and output that you'd expect to see? Just a few rows of different data so that we can see how the formula needs to work?

[MZING81]

I just need to really know how to connect muliple text strings within my if(isnumber formula, without using a * or & symbols, because those symbols dont function function as an either or.

[MZING81]

Thank you, but the srting is in a single column. I just need to figure an, either, or with the if(isnumber( formula that I have above.

Thanks

[Ken Puls]

The strings are about 75 charcters of data with most of the info not being that necessary, I just want to pull out the necessary portions. There are about 14 different text strings that I want to extract. I've been using text to columns, but that has been creating issues with sumproduct formulas that will not account for the data, even though its there.

And that's exactly why a sample would be helpful to see. I'm not sure that a SUMPRODUCT will get you what you want. It may, but I'm not sure. You could nest a whole bunch fo IF statements, but it's going to be a logistical nightmare to maintain. But if we can see what we're working with, then it will make it a LOT easier to help as we can suggest other methods that may be better. As it is, we can write formulas, but we'll be guessing.

[MZING81]

I would have to create a dummie data to replace the existing personal data, thats alot more work than is necessary. All that I need is what i can use to have look for an additional "text" another Blah-3 along with that Blah-2. Ampersands dont work and neither does a comma or an wild-card. All I need is it to say if I cant find Blah-2 then look for Blah-3 etc....

=IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

And that's exactly why a sample would be helpful to see. I'm not sure that a SUMPRODUCT will get you what you want. It may, but I'm not sure. You could nest a whole bunch fo IF statements, but it's going to be a logistical nightmare to maintain. But if we can see what we're working with, then it will make it a LOT easier to help as we can suggest other methods that may be better. As it is, we can write formulas, but we'll be guessing.

[Ken Puls]

Alright, whatever. If you can't be bothered to provide sample data, I'm not going to build any to test the function. If it's a waste of your time, then it's certainly a waste of mine.

The IF function works like this:
=IF(Test,Value if TRUE, Vale if FALSE)

So, if this works: =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

Then just nest your second IF in place of the "":

=IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3","")

And for the next you continue the process:

=IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3",IF(ISNUMBER(SEARCH("*BLAH-4*",J5)),"BLAH-4",""))

And so on...

[MZING81]

if function.xlsxYour right Ken, I shouldve posted the workbook but I didnt think it was necessary cause I thought it was an easy fix, and it really wasn't. I tried adding the comma between IF statements but that only resulted in an value data error.
if function.xlsx


So here's the workbook. Sorry for the hassle.

Alright, whatever. If you can't be bothered to provide sample data, I'm not going to build any to test the function. If it's a waste of your time, then it's certainly a waste of mine.

The IF function works like this:
=IF(Test,Value if TRUE, Vale if FALSE)

So, if this works: =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

Then just nest your second IF in place of the "":

=IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3","")

And for the next you continue the process:

=IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3",IF(ISNUMBER(SEARCH("*BLAH-4*",J5)),"BLAH-4",""))

And so on...