Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-03-2021, 08:47 AM
oscarlimerick oscarlimerick is offline Excel inverse search or find Windows 8 Excel inverse search or find Office 2013
Advanced Beginner
Excel inverse search or find
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default 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
Attached Files
File Type: xlsx excel sample.xlsx (8.2 KB, 4 views)
Reply With Quote
  #2  
Old 05-03-2021, 10:03 AM
jeffreybrown jeffreybrown is offline Excel inverse search or find Windows 10 Excel inverse search or find Office 2016
Expert
 
Join Date: Apr 2016
Posts: 650
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

The easiest way would be to apply conditional formatting.

Conditional Formatting
  • Highlight applicable range >> B4:B9
  • Home Tab >> Styles >> Conditional Formatting >> New Rule
  • Select a Rule Type: Use a formula to determine which cells to format
  • Edit the Rule Description: Format values where this formula is true: =RIGHT(B4,3)<>"mp3"
  • Format… [Number, Font, Border, Fill]
  • OK >> OK
Reply With Quote
  #3  
Old 05-03-2021, 10:09 AM
Kevin@Radstock Kevin@Radstock is offline Excel inverse search or find Windows 10 Excel inverse search or find Office 2019
Office 365
 
Join Date: Feb 2012
Posts: 94
Kevin@Radstock is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-03-2021, 10:28 AM
oscarlimerick oscarlimerick is offline Excel inverse search or find Windows 8 Excel inverse search or find Office 2013
Advanced Beginner
Excel inverse search or find
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 05-03-2021, 10:40 AM
oscarlimerick oscarlimerick is offline Excel inverse search or find Windows 8 Excel inverse search or find Office 2013
Advanced Beginner
Excel inverse search or find
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 05-03-2021, 01:54 PM
jeffreybrown jeffreybrown is offline Excel inverse search or find Windows 10 Excel inverse search or find Office 2016
Expert
 
Join Date: Apr 2016
Posts: 650
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

You are very welcome.

The 3 simply means, look at the Right 3 characters in B4.
Reply With Quote
  #7  
Old 05-04-2021, 03:18 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel inverse search or find Windows 7 64bit Excel inverse search or find Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,507
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

RIGHT, RIGHTB functions - Office Support
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inverse Grayscale in Slideshow mode esersen PowerPoint 1 06-29-2014 11:19 PM
Excel inverse search or find Find what box in Find and replace limits the length of a search term Hoxton118 Word VBA 7 06-10-2014 05:05 AM
Excel inverse search or find 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
Excel inverse search or find How to get the inverse of the values in the same table? Jamal NUMAN Excel 4 04-16-2011 06:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft