Search function suddenly case-sensitive?

hairball101

New Member
Joined
May 23, 2016
Messages
14
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,370
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Welcome to the forum

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

hairball101

New Member
Joined
May 23, 2016
Messages
14
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,370
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
May 23, 2016
Messages
14
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,370
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad you got it sorted out! :biggrin:
 

Forum statistics

Threads
1,082,478
Messages
5,365,780
Members
400,849
Latest member
candy2052

Some videos you may like

This Week's Hot Topics

Top