National Insurance Number Conditional Formatting

[spacemaid12]

Hi,

I have a spreadsheet where I have to input National Insurance numbers and I want to run some conditional formatting so that if a temporary NI number is input it highlights the cell.

The NI number cannot begin TN, there is then a string of 6 digits and thisis followed by either an A,B,C or D.

Can anyone help?

Thanks,

 

[barry houdini]

Does a temporary number begin with TN?

If the numbers are in column A then select column A and use conditional formatting with "formula is" option and the formula

=LEFT(A1,2)="TN"

select required formatting > OK

 

[spacemaid12]

Hi,

Temporary NI numbers begin with TN.

Andf the formula suggested works perfectly, I am not struggling with the final character which can only be A,B,C or D.

I have tried loads of different combinations and non work.

I thought this would work but it's not:

=RIGHT(N4,1)=">E"

Any help would be greatly appreciated.

Thanks,

 

[barry houdini]

You could try like this

=(CODE(RIGHT(N4))<65)+(CODE(RIGHT(N4))>68)

 

[spacemaid12]

I managed to get it to work with:

=RIGHT(N4,1)>"D"