Replace text with conditional formatting over formula

[shaun86]

Hello, I am attempting to replace text using a conditional format in a cell that is using a formula to generate a text string. Here is my formula:

=IF(A1>"",B1&" "&LEFT(C1,2),"")

Basically it combines text from B1 with the number value from the first part of C1 to create results like "Car 01" or "Car 04"

What I want the conditional format to do is replace any cell that says "Car 04" to "Car 01"

I've tried using a formula condition to format where true =D1="CAR 01", then under Format > Number > Custom entering "Car 04", but it isn't changing. Just curious if I'm missing a step. Is the formula result overriding the condition?

 

[Fluff]

Conditional formatting can't change the value of a cell.
Also custom number formatting only works for numbers, not text

 

[Joe4]

Conditional Formatting does not/can not replace the values returned by cells, only the formatting of the cell.

To do what you want, try updating your formula like this:

=SUBSTITUTE(IF(A1>"",B1&" "&LEFT(C1,2),""), "Car 04","Car 01")

 

[shaun86]

Thanks for the response! I will try to come up with another method for this.

 

[Yongle]

or amend the formula in the cell to

=IF( IF(A1>"",B1&" "&LEFT(C1,2),"")="Car 04", "Car 01", IF(A1>"",B1&" "&LEFT(C1,2),"") )

 

[Joe4]

Thanks for the response! I will try to come up with another method for this.

Does the formula I posted work for you?

 

[shaun86]

Does the formula I posted work for you?

Yes sorry I was a bit hasty there. It worked perfectly, thank you very much. I have not used the Subtitute function before it is always nice to learn how to use new functions.

 

[Joe4]

You are welcome.
The advantage to using SUBSTITUTE is that you do not need to list your original IF function twice in the revised function.