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?
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
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?
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
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.
Code:=IFERROR(INDEX($F$2:$F$50,MATCH(1,SEARCH("*"&$F$2:$F$50&"*",A2),0)),"")
Hope it's a little help
Last edited by Canapone; 2012-12-21 at 11:29 AM.
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.
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.
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
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, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
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","")
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...
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
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.
Bookmarks