Solved Search a string for a case-sensitive term

November 21, 2018 at 08:23:11
Specs: Windows 10
Hi.

I need to search an excel column that contains commentaries, i.e. free text entries, to find any cells that contain either "CAS", "CASS" or "MOF" within the commentaries.

I can't use autofilters because they don't recognise case, plus they return cells containing words like "cash" etc.

I've found some google results that talk about using an EXACT formula and then filtering on the TRUE results, but it looks like that would only work if my column only contained upper or lower case versions of the terms I was looking for.

Any ideas?


See More: Search a string for a case-sensitive term

Reply ↓  Report •

#1
November 21, 2018 at 09:43:56
Use the SEARCH() function, it is not case sensitive.

In it's simplest form:

=SEARCH(A1,B1)

A1 contains your Search word
B1 is the Target cell to search

What do you need returned?
Do they appear, A count of how many times they appear, what row they appear on?

Unfortunately, I'm off to grandma's house for the Thanksgiving holiday,
and will not be back till Friday.If you need more assistance,

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
November 21, 2018 at 11:05:45
Say Happy Thanksgiving to Grandma for me. ;-)

message edited by DerbyDad03


Reply ↓  Report •

#3
November 21, 2018 at 14:36:38
✔ Best Answer
This method should highlight any cell that contains one or more of your strings, at least based on my testing. Obviously I don't have your data, so I can't be sure it will work for you.

I am making the assumption that your data resides in A1:A10. Modify this suggestion as required.

1 - Select A1:A10
2 - Open the Conditional Formatting dialog box
3 - Choose "Use a formula..."
4 - Paste this into the Formula field:

=OR(ISNUMBER(FIND(" CAS ", " "&A1&" ")),ISNUMBER(FIND(" CASS ", " "&A1&" ")),ISNUMBER(FIND(" MOF ", " "&A1&" ")))

5 - Click the Format... button and choose a Format, such as a fill color.
6 - OK your way back out.

Any cell that contains any one or more of your three strings - upper case CAS, CASS or MOF, should be highlighted in the color you chose.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
November 23, 2018 at 01:28:19
Perfect DerbyDad! That was exactly what I was looking for. Thanks very much. And thanks to Mike for responding as well.

Happy Thanksgiving.


Reply ↓  Report •

#5
Reply ↓  Report •

Ask Question