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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112
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]
 

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398
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)
 

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112
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
 

MandeepBaluja

Board Regular
Joined
Jan 28, 2014
Messages
120
Select cells and apply this formula =ISNUMBER(SEARCH($I$2,G5)) your search value should be in I2
 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

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
Top