I want to create a formula that matches certain characters typed by a users input.
If Brian is typed in a cell, I want it to search a range and highlight those names that start with Brian. Even if there are more characters after Brian.
Any help would be appreciated.
Select range B5:B23 and go to Format|Conditional Formatting
Select Formula Is from 1st drop down menu and enter formula: =SEARCH($E$5,$B5)
click Format and select colour from Pattern tab.
Click Ok.
Click Ok.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
That is great, but can I make it start at the start of the line of text for the search?
Bill
Rob
Search for B and make sure it starts at left of the text so it finds Bill first or all names that start with B and not include Rob.
use formula:
=Left($B5,"B") you can replace "B" with cell reference housing the letter.
or if you want the name in E5 to start at the left..
=Left($B5,Length($E$5))=$E$5
I can't seem to get it. Sorry
How would the second formula you wrote look with the first formula you wrote in the conditional format menu?
This is what I have and it is not working
=SEARCH(LEFT($B5,Length($E$5))=$E$5,$B5)
Just use the formula (ie. replace previous formula):
=Left($B5,Length($E$5))=$E$5
It doesn't need to be put in the Search() function...
This is what I have and it does not seem to work. Sorry, I am not sure what I am doing wrong.
Sorry.... my bad.... Length should just be LEN.... I should have tested that...
=LEFT($B5,LEN($E$5))=$E$5
Works great now, THANKS!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks