#1
|
|||
|
|||
Excel inverse search or find
Good day excel forum experts. Is there a way, using excel, to find a cell that does NOT contain something. In the attached spreadsheet, all the cells in Column B, except for cell B8, contain the title name of the song ending with .mp3. This is just a small section of a huge list. If I do a search on "*.mp3", excel will identify all cells that have the .mp3 in them (which would be cells B4-B7 and B10).
But what I want to do is have it search for items in column B that DON'T contain the *.mp3, in other words I want it to flag cell B8 for my example. Is there any way to do this? Many thanks |
#2
|
|||
|
|||
The easiest way would be to apply conditional formatting.
Conditional Formatting
|
#3
|
|||
|
|||
Hi
=RIGHT($B4,3)<>"mp3" & copy down. Use in conditional formatting to highlight B8 (in red), select range B4:B9 Home > Conditional Formatting > New rule > Use a formula to determine which cells to format > In the "Format values where this formula is true:" enter the formula above > format > your requirements. |
#4
|
|||
|
|||
Yes, that worked perfectly, but can you please tell me what the 3 means in the formula "=right (B4,3)<>"mp3"? B4 is probably the starting cell in the range, but what does the 3 do? Many thank
|
#5
|
|||
|
|||
Yes, this formula works as well, but can you please explain what the 3 in B4,3 means? I understand the rest of the conditional formatting, but what does the value 3 do? Thanks
|
#6
|
|||
|
|||
You are very welcome.
The 3 simply means, look at the Right 3 characters in B4. |
#7
|
||||
|
||||
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Inverse Grayscale in Slideshow mode | esersen | PowerPoint | 1 | 06-29-2014 11:19 PM |
Find what box in Find and replace limits the length of a search term | Hoxton118 | Word VBA | 7 | 06-10-2014 05:05 AM |
How to obtain the inverse tan of a value in degree/minute second? | Jamal NUMAN | Excel | 3 | 07-27-2013 10:18 AM |
Won't find in search | ep2002 | Word | 3 | 10-31-2012 05:24 PM |
How to get the inverse of the values in the same table? | Jamal NUMAN | Excel | 4 | 04-16-2011 06:24 AM |