Insert data with length between 11 OR 15 digits

T

Tan Nico

Hi,

I want to create the formula than can calculate the Length of cell is 11 or
15 dig (in one formula only), and the blank cell (null) is doesn't count for.

I preffered the formula to be inserted ini Conditional Formula, and if
there's any cell that contain data not 11 or 15 dig, it's cell gonna have a
red background (ps. the blank cell also not to be count, cause I want to copy
to single coloumn)

If anyone could help me on this formula, before thank you first to you.

Thanx. Nico.
 
E

excelent

insert in Conditional format and pick ur color

=IF(A1<>"",AND(LEN(A1)<>11,LEN(A1)<>15))

This dont count spaces in text
=IF(A1<>"",AND(LEN(SUBSTITUTE(A1," ",""))<>11,LEN(SUBSTITUTE(A1," ",""))<>15))


"Tan Nico" skrev:
 
T

T. Valko

So, you want the cell fill color to be red if the cell is not empty and
contains anything other than a number between 11 and 15 digits long?

Is the number an integer? Might there be any leading 0's?

Try this:

Assume the cell in question is A1
Select cell A1
Goto Format>Conditional>Formatting
Formula Is:

=OR(ISTEXT(A1),AND(ISNUMBER(A1),OR(LEN(A1)<11,LEN(A1)>15)))

Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

Biff
 
T

T. Valko

=IF(A1<>"",AND(LEN(A1)<>11,LEN(A1)<>15))

That fails if the entry is text and is 11 or 15 characters in length.

Biff
 
Top