Checking column data

[rszlp]

Hi,

I have been searching through the boards to try and find an answer to this question but nothing seems to fit, so maybe someone can help me.

Within in my spreadsheets I need to check that data in Column A contains exactly 8 characters. Whilst the values are different the format is the same, every entry starting and ending with a letter interspaced by 6 numbers.

Is there a simple formula I can use to check the column and highlight any entries that do not match that criteria.

Many thanks

[oldchippy]

Hi,

I have been searching through the boards to try and find an answer to this question but nothing seems to fit, so maybe someone can help me.

Within in my spreadsheets I need to check that data in Column A contains exactly 8 characters. Whilst the values are different the format is the same, every entry starting and ending with a letter interspaced by 6 numbers.

Is there a simple formula I can use to check the column and highlight any entries that do not match that criteria.

Many thanks

Hi rszlp,

I'm assuming that there are no spaces in your data in column A, if so

=IF(LEN(A1)=8,"OK","")

oldchippy

[stevebriz]

this can be done with macro if you are interested

This changes the cell color to blue if length is 8 characters but with wrong start or wrong end character.

If the cell is empty or has less or more than 8 characters it makes the cell red

Please Login or Register  to view this content.

[rszlp]

Stevebriz and Oldchippy many thanks for your quick reponses.

oldchippy - works great no problems

Stevebriz - Would like to try your code though I am a bit of a novice at excel, can you tell me how i would apply that code within my worksheet to get it to run.

Once again many thanks