Search function suddenly case-sensitive?

[hairball101]

Greetings all,

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

 

[RoryA]

Welcome to the forum

Can you give a specific example of how the formula is not working?

 

[hairball101]

Welcome to the forum

Can you give a specific example of how the formula is not working?

Certainly.

Let's say that Text1 [Cell B3] is "adjustable strap", so that's the word being searched for the cells in Cell1 [G:G]. If Cell1a [G2] contained "This is a test for adjustable strap." and Cell1b [G3] contained "This is a test for Adjustable Strap.", it would only trigger a match with Cell1a [G2]. Conversely, if Text1 [B3] was "Adjustable Strap", it would only trigger a match with Cell1b [G3].

Keep in mind that the formula is copied down several rows, so G2 and G3 would be affected by the same formula, but obviously it would be tweaked to search within the respective row. B3 is static throughout.

 

[RoryA]

We need to know what is in the other cells referred to by the formula and what the outcome is, versus what you think it should be. Have you used the formula evaluator tool to see what's going on?

 

[hairball101]

We need to know what is in the other cells referred to by the formula and what the outcome is, versus what you think it should be. Have you used the formula evaluator tool to see what's going on?

I apologize for the delayed reply. I thought that I had investigated all that I could on my end, but your comment regarding the formula evaluator tool got me down the right track. One of the other cells that the formula is referring was using SUBSTITUTE, which, as I've now remembered, is case sensitive. It was this SUBSTITUTE function that was causing my problem, not the ISNUMBER/SEARCH combo. I've corrected what I needed to correct.

Thanks for setting my brain on the proper path I'd say this issue is resolved.

 

[RoryA]

Glad you got it sorted out!