Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
character length in data forms | Excel Discussion (Misc queries) | |||
copy, insert and add digits | Excel Worksheet Functions | |||
Need insert a dash in between last 4 digits in text string | Excel Worksheet Functions | |||
Graph with variable data length | Excel Discussion (Misc queries) | |||
length of character data | Excel Discussion (Misc queries) |