conditional format last letter before numbers

[grizzly6969]

my cells contain a name then an "N" or "D" before a number between 1-12
If there is an N I would like to colour red and a D would be coloured Green
so a names like Dan Noon D12 would be coloured Green due to D12
a name like Nancy Day N8 would be coloured Red due to N8

[MarvinP]

Hi Grizzly,

Try this attachment. I used the first letter after the last space instead of what you suggested. I think it will work for your sample text. See attached and the conditional formatting formulas. Just ask if you need an explaination.

[Md Aejaz]

my cells contain a name then an "N" or "D" before a number between 1-12
If there is an N I would like to colour red and a D would be coloured Green
so a names like Dan Noon D12 would be coloured Green due to D12
a name like Nancy Day N8 would be coloured Red due to N8

Hi grizzly6969 ,

i think it will work as per your requirment.format.xls

[grizzly6969]

Thanks MarvinP works great

[grizzly6969]

your formula works equally as well Md Aejaz --- many thanks

[puru.sve]

Use below formula in your conditional formatting

LEFT(TRIM(RIGHT(A1,3)))="D" ' make pattern red
LEFT(TRIM(RIGHT(A1,3)))="N" ' make pattern green

select cell A1 then go to in conditional formatting--->> manage Rules --> use formula to determine which cell format -->place the LEFT(TRIM(RIGHT(A1,3)))="D" in formula bar option and set the pattern red ...


do same for green ... select cell A1 and repeat the above process and place green pattern with LEFT(TRIM(RIGHT(A1,3)))="N"

then after copy the cell A1 and paste it in rest cells as formatting special

[puru.sve]

Sorry guys , I just posted late ... dont mind.

[grizzly6969]

I tried your formula Puru.Sve and worked as well ---- thanks to all who helped