Check if Cell contains x amount of numbers plus a letter

[hmltnangel]

Just as it says on the tin folks.

Is there anyway to get excel 2010 to conditional format a cell if the cell contains (for instance) a six digit number followed by a letter? (123456A)?

thanks

 

[Rick Rothstein]

Just as it says on the tin folks.

Is there anyway to get excel 2010 to conditional format a cell if the cell contains (for instance) a six digit number followed by a letter? (123456A)?

Assuming the active cell is A1, give this Conditional Formatting formula a try...

=AND(ISNUMBER(-LEFT(A1,6)),ISTEXT(RIGHT(A1)))

 

[Rick Rothstein]

Assuming the active cell is A1, give this Conditional Formatting formula a try...

=AND(ISNUMBER(-LEFT(A1,6)),ISTEXT(RIGHT(A1)))

Actually, we need to also check the length of the value in the cell as well...

=AND(ISNUMBER(-LEFT(A1,6)),ISTEXT(RIGHT(A1)),LEN(A1)=7)

 

[hmltnangel]

Excellent, thanks :D

Seems to work fine. I have around 40 entries to check now nstead of 1600 .... massive time saver. thanks again.

 

[Peter_SSs]

Seems to work fine.

I disagree. I have used that CF in my first screen shot below. As you can see, it has incorrectly highlighted the first 4 entries.

Excel Workbook
	A
1	1DEC189
2	6985648
3	123E45X
4	6 6
5	569845R
CF1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(ISNUMBER(-LEFT(A1,6)),ISTEXT(RIGHT(A1)),LEN(A1)=7)	Abc

Instead, I would suggest this CF formula.

Excel Workbook
	A
1	1DEC189
2	6985648
3	123E45X
4	6 6
5	569845R
CF2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(MAX(ABS(52.5-CODE(MID(A1,ROW(INDIRECT("1:6")),1))))<5,ABS(77.5-CODE(UPPER(RIGHT(A1))))<13,LEN(A1)=7)	Abc

 

[hmltnangel]

Thanks Peter,

I was just coming back to say id found errors in it . Also a few changes to exactly whats needed, the numbers have to be exactly 7 digits with either one or two letters after it. If I can understand what each part of the code is doing, then I can make the amendment. I'll have a mess with it and see if I can make it work.

 

[Peter_SSs]

the numbers have to be exactly 7 digits with either one or two letters after it.

Try this one.

Excel Workbook
	A
1	8932658D
2	9865489Xy
3	1111E11X
4	6 6
5	569 845R
6	5708845R
7	0000000RQ
8	23659874Z
9
10
CF3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(MAX(ABS(52.5-CODE(MID(A1,ROW(INDIRECT("1:7")),1))))<5,MAX(ABS(77.5-CODE(MID(UPPER(A1)&"A",ROW(INDIRECT("8:9")),1))))<13,ABS(8.5-LEN(A1))=0.5)	Abc