Solved: Trim formula needed in condiational formatting

[obriensj]

Hi,

I want to use conditional formatting (formula is) to trim the value in a cell to pick up the word ?final? and then format another cell in bold.
Basically I have the text ?qtr 1 figures final? in cell A5 and I want the figure in cell M5 which is 100 to be put in bold only when the text ?final? is found in column A.
I have tired numerous trim?s and right trims but nothing.

Any ideas?

Thanks

[mdmackillop]

=SEARCH("final",A5)>0

[Bob Phillips]

=ISNUMBER(SEARCH("final",A5))

[obriensj]

Thanks Guys worked.

[obriensj]

Hi, might have been a bit premature is saying this is fixed!
The below code works when I know what cells the text ?final? will appear in.
The spreadsheet I have in column A the text ?final? could appear anywhere from A2 to A6000 I have just discovered.
Is there anyway the below can accommodate searching in the column itself?
I tried changing A5 to A:A but nothing.
Any ideas?


=ISNUMBER(SEARCH("final",A5))

Thanks

[Bob Phillips]

=COUNTIF(A:A,"*final*")>0

[obriensj]

Thanks XLD, still not exactly what i want though!
I should probably explain better, my fault.
I attach the sample file which may help.
The problem is that in column M where i want the formatting done it will only apply to one cell only, cell M10.
This is becuase just put the formatting on cell M10, i thought i could highlight column M and format but cant do.

The issue being that M21 and M26 should also be formatted to be in bold like M10. However i will never know where to start on Column M as i will not know where the word final will appear in column A!
Was thinking of doing some sort of loop in VB would this do it?

Thanks

Steve

[Bob Phillips]

You select all of the cells in column M and then apply CF with a formula as given earlier of

=ISNUMBER(SEARCH("final",$A1))

set the format and exit.

[obriensj]

Hi,

Tried that still no luck, must be having a bit of a blonde moment!
I tried this:
=ISNUMBER(SEARCH("final",$A1)) and also this:
=COUNTIF(A:A,"*final*")>0

I have highligted the cells M2:M26 and gone into conditional formatting and copied both of these formula's.
This formula: =COUNTIF(A:A,"*final*")>0 formats the whole of column M when i just need M10, M21 and M26 formatted.

Any ideas?

Thanks

[Bob Phillips]

.

[obriensj]

Thanks XLD, works a treat.

Steve