conditional format based on first 3 letters of a name

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398
good morning
i have a long list of names in column A.(A2:A400 actually) im having trouble finding the name i want. can i type the first 3 letters of a name or just the first name in A1 and have the matching cell fill color yellow? there are many cells in that column that are blank or filled with numbers.

bill smith would fill yellow if i type bill or bil, whatever is easier.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi Bmuddy2

is this what you looking for?

the true/false formula is shown at F4, just type it on F5
the search you gonna type is on C2

the conditional formatting > select Cell C5 > apply conditional formatting "use formula to determine"
and click F5, make sure it's written $F5=TRUE, you need the $ only in front of the column, not in row.

try it. :)
have a nice day,

cheers
160af53.jpg
[/IMG]
 
Upvote 0
thanks for the reply, formula works fine, now trying to get the conditional formatting to work. question, each cell contains a first and last name, will this only work if the first and last name are entered in C2? or can i just enter in the first name? ( sorry wasnt clear when i mentioned the cell contents)
 
Upvote 0
Hi,

Yes you can do it with last name.

example :
Bill Brown
Sue Moore
April May

:) lol

the search will works if you search it only part of 1st or Last name only

search example that success:
brown
bro
Moo
May

search example that won't be success :
Bill B
Sue M
Apr May


if you want to make the latter combination success, my suggestion you need to create 1 more column which there's no (_) space between first & last (you can do it by find & replace).
1. when its already combined (ex : BillBrown, SueMoore, etc)
2. place the true/false checking to the name column without space
3. while conditional formatting formula still the same,
4. you can now search it with (ex : BillB, SueM, AprilMay)

thanks
 
Upvote 0

Forum statistics

Threads
1,211,864
Messages
6,104,443
Members
447,909
Latest member
isadayan16

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top