IF Function based on previous conditional formatting

[LJREdinburgh]

Hi,

I am going through a large dataset of about 5000 records with 5 columns. Column D is an e-mail address field on which I have applied conditional formatting to highlight RED if the e-mail address contains "bt" anywhere in the address.

What I am trying to do in Column F is to create an IF formula that will display the text "BT" if a cell in column D is red and if not leave it blank. There might be a way around this without the initial conditional formatting, for the IF formula in column F. However, I can't seem to figure it out and I do not want to go through all the records and manually type BT next to all Red cells.

Thanks

 

[VoG]

Try like this

=IF(ISNUMBER(SEARCH("bt",D1)),"BT","")

 

[Alba65]

Cant you use the formula from the conditional formatting inside the IF one, something along the lines of:

IF(your CF formula=true,"BT,"")

 

[LJREdinburgh]

This is exactly what I was after. Cuts out the conditional formatting stage. Works perfectly!

I am trying to understand the formula. How is the formula changing what is referring to numbers into text?

 

[LJREdinburgh]

Try like this

=IF(ISNUMBER(SEARCH("bt",D1)),"BT","")

This is exactly what I was after. Cuts out the conditional formatting stage. Works perfectly!

I am trying to understand the formula. How is the formula changing what is referring to numbers into text?

 

[VoG]

The SEARCH function returns a number (character position) if bt is found, otherwise an error. ISNUMBER checks whether it returns a number.

 

[LJREdinburgh]

The SEARCH function returns a number (character position) if bt is found, otherwise an error. ISNUMBER checks whether it returns a number.

Thanks, breaking it down like that adds to the understanding of the formula!

 

[LJREdinburgh]

Could you tweak this formula to pick up multiple texts?
Lets say I wanted this to pick up if the e-mail addres had "hotmail" "live" "msn" or "microsoft" and display the text "windows domain" ?

 

[LJREdinburgh]

Try like this

=IF(ISNUMBER(SEARCH("bt",D1)),"BT","")

Could tweak this formula to pick up multiple texts?
Lets say I wanted this to pick up if the e-mail addres had "hotmail" "live" "msn" or "microsoft" and display the text "windows domain" ?

 

[VoG]

There must be a neater way but try

=IF(OR(ISNUMBER(SEARCH("hotmail",D1)),ISNUMBER(SEARCH("live",D1)),ISNUMBER(SEARCH("msn",D1)),ISNUMBER(SEARCH("microsoft",D1))),"windows domain","")