Insert data with length between 11 OR 15 digits
 

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.

Insert data with length between 11 OR 15 digits
 

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:

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.

Insert data with length between 11 OR 15 digits
 

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 FormatConditionalFormatting
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

"Tan Nico" <Tan wrote in message
...
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.

Insert data with length between 11 OR 15 digits
 

=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

"excelent" wrote in message
...
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:

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.