[RESOLVED] Formula to check the length of the value

hi guys, is there a formula in conditional formatting to check the length of the value in all the cells in one column? or it's only possible through a macro? thanks... :confused:

are you wanting to simply resize your column to the widest one? Also, is this excel?

Why check the length in conditional formatting? What do you want to do with it?
Usually you can use a formula to check the length as:
=LEN(A1)
That can also be used in conditional formatting.

hi anhn, thanks for the reply... yup.. i already created a macro to do this task..

but just want to know whether it can be simply done by a formula..

like to check the duplicates just use the countif formula and excel will automatically find the duplicate and highlight also...

so..i just want to know whether there is a formula like "=len(a1)" but this formula will check for only one cell, how to check all the cells in one column and highlight also if the length is less than the specified length....

Quote:

---

why i want to check length using conditional formatting?
>>>because i got a hundreds of data in one column and the length should be 9 digits.. so to make sure that there are no typo errors i need to check the length of each cell.

---

thanks in advance...

Select the range such as [A2:A200] (with [A2] is active cell) that you want to set Conditional Formatting.
In CF, select Cells Formula, type =LEN(A2) then choose your own format.
The formula for all other cells from [A3] to [A200] will be auto created with =LEN(A3) ... =LEN(A200)

Nothed that, if set formula as =LEN($A$2) then all other cells will be the same with =LEN($A$2).

thanks anhn for the reply..so how to use the if statement in the len formula..for example if the length is less than 9 digits it will put a mark x on column b.. or is it possible to highlight the cell ... thanks.......

Set Cells Formula =(LEN(A2) < 9) then click Format, set Pattern with color of your choice.

thank you anhn, you're simply the best :)