#### hairball101

##### New Member

- Joined
- May 23, 2016

- Messages
- 14

I've been a frequent visitor here over the past few months, so I'd like to start by saying thanks to all who answer these types of questions. Ya'll have been quite helpful, even to those who didn't ask the question :D

The official question: Would there be some trigger that could cause the SEARCH function to become case-sensitive, particularly if it's being used in a complex formula with several IF statements?

A short background: I've been building an excel tool to help generate search terms for a website's search engine optimization. Basically, it will search a cell (containing marketing info or product copy) for a specific keyword and if that keyword appears in the cell, it will add another given term to the end of a second string of text. This second string will be the search terms that a customer may search for on the site, such as "Sporty" or "Backpack". If the given term already exists within the second string, then the tool will not add it (since it'd be a duplicate) even if the first keyword is found within the first cell. Given that I've built the tool to save time, this is performed across several hundred rows simultaneously.

Another way of looking at what the tool does is if Text1 exists within Cell1, then it'll check if Text2 exists within Cell2. If Text2 does not exist yet within Cell2, then it'll add Text2 to Cell2. If it already exists, it won't add it.

I didn't want the tool to be case-sensitive, so I've been using the ISNUMBER/SEARCH combination to check for these phrases within each cell. Despite this, it looks like it is somehow case-sensitive. I have no clue why this is the case. The core formula for this tool is pretty long and it does include the FIND function, but it isn't in a place that it would affect case-sensitivity for the output.

Below is the core formula for the tool. Given my new account status here, I can't currently attach files, thus I'm left to describe what the various cells/ranges represent. In keeping with the example above, B3 would be Text1, C3 would be Text2, G2 would be Cell1 and H2 would be Cell2. Column AH contains specific terms that are case-sensitive if they were to appear as Text2 (C3), column AE helps to check for anti-terms (non-this, faux-that) to eliminate false positives, and AG checks terms to ensure they aren't intended for specific items. Cell C5 determines where within Cell2 the new Text2 should be added if applicable. The line breaks are intentionally added within the formula.

I apologize for the complex question and succinct formula explanation. If any clarification is required, I'll be more than happy to elaborate where I can. Much thanks for at least taking a look

Here is the formula: =IF(OR(G2="",$B$3="",$C$3=""),IF(AND(G2="",NOT(H2=""),COUNTIF(G:G,"*")>0,NOT(OR($B$3="",$C$3=""))),H2,IF(OR($B$3="",$C$3=""),"",H2)),IF(AND(G2="",NOT(H2="")),H2,IF($C$5="Last",IF(OR(ISNUMBER(SEARCH(IF(ISNUMBER(SEARCH($C$3,$H2)),IF(TRIM($C$3)=LEFT($H2,LEN(TRIM($C$3))),"",","),"")&$C$3&",",H2,1)),AND(LEN($C$3)=LEN(H2),$C$3=H2),RIGHT(H2,LEN($C$3))=$C$3),H2,IF(AND(AND(NOT(LEFT($G2,9)="Color Add"),NOT(LEFT($G2,8)="Size Add"),NOT(LEFT($G2,9)="Duplicate")),AND(NOT(ISNUMBER(SEARCH($B$3&" free",$G2,1))),NOT(ISNUMBER(SEARCH($B$3&"-free",$G2,1))),NOT(ISNUMBER(SEARCH($B$3&"-less",$G2,1))),NOT(ISNUMBER(SEARCH("non-"&$B$3,$G2,1))),NOT(ISNUMBER(SEARCH("anti-"&$B$3,$G2))),NOT(ISNUMBER(SEARCH("not "&$B$3,$G2,1))),NOT(ISNUMBER(SEARCH("faux "&$B$3,$G2,1))),NOT(ISNUMBER(SEARCH("faux-"&$B$3,$G2,1))),$AE2=TRUE),OR(ISNUMBER(SEARCH($B$3&" ",G2,1)),ISNUMBER(SEARCH($B$3&"•",$G2,1)),ISNUMBER(SEARCH($B$3&".",G2,1)),ISNUMBER(SEARCH($B$3&"-",G2,1)),ISNUMBER(SEARCH($B$3&",",G2,1)),ISNUMBER(SEARCH($B$3&"&",G2,1)),ISNUMBER(SEARCH($B$3&CHAR(34),G2,1)),ISNUMBER(SEARCH($B$3&"<",G2,1)),ISNUMBER(SEARCH($B$3&">",G2,1)),ISNUMBER(SEARCH($B$3,SUBSTITUTE(SUBSTITUTE($G2,""," "),"

"," "),1)))),IF(H2="",IF(COUNTIF(AH:AH,$C$3)>0,TRIM($C$3),TRIM(PROPER($C$3))),H2&","&IF(COUNTIF(AH:AH,$C$3)>0,TRIM($C$3),TRIM(PROPER($C$3)))),IF(H2="","",H2))),IF(OR(ISNUMBER(SEARCH(IF(ISNUMBER(SEARCH($C$3,$H2)),IF(TRIM($C$3)=LEFT($H2,LEN(TRIM($C$3))),"",","),"")&$C$3&",",H2,1)),AND(LEN($C$3)=LEN(H2),$C$3=H2),RIGHT(H2,LEN($C$3))=$C$3),H2,IF(ISNUMBER(SEARCH($B$3,G2,1)),IF(H2="",IF(COUNTIF(AH:AH,$C$3)>0,TRIM($C$3),TRIM(PROPER($C$3))),IFERROR(IF($C$5=1,"",LEFT(H2,(FIND(";",SUBSTITUTE(H2,",",";",$C$5-1)))))&IF(COUNTIF(AH:AH,$C$3)>0,TRIM($C$3),TRIM(PROPER($C$3)))&IF($C$5=1,"",",")&IF($C$5=1,","&H2,RIGHT(H2,LEN(H2)-(FIND(";",SUBSTITUTE(H2,",",";",$C$5-1))))),H2&","&IF(COUNTIF(AH:AH,$C$3)>0,TRIM($C$3),TRIM(PROPER($C$3))))),IF(H2="","",H2))))))