CODEWITHSUNDEEP
excelexcel-formulaformula
Dodlle
Dodlle

Reputation: 11

Excel, check if a column has the same amount of characters for each cell

I would like to add a check to my worksheet in Excel, to test if entries in a column have the same total number of characters. I know how to do it for two cells where I can simply use =IF(EXACT(LEN(A1),LEN(A2)),"Match","No Match") but what if I have a lot of cells to test against each other?

Example:

example image

Upvotes: 1

Views: 1621

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

Try this array formula (entered by holding down ctrl + shift while hitting enter):

=IF(AND(LEN(OFFSET($A$1,0,0,COUNTA($A:$A)))/LEN($A$1)=1),"Match","No Match")

or this normally entered formula:

=IF(AND(MMULT(LEN(OFFSET($A$1,0,0,COUNTA($A:$A))),1/LEN($A$1))=1),"Match","No Match")

You could also use the Std Dev calculation, as suggested by others, in an array (CSE) formula so as to avoid the helper column:

=IF(STDEV.P(LEN(OFFSET($A$1,0,0,COUNTA($A:$A)))),"No Match","Match")

Upvotes: 0

Jamie Bull
Jamie Bull

Reputation: 13539

Add a helper column B with =LEN(A1), etc in each row, then a check cell in say C1 with =STDEV.P(B:B)=0. This will show TRUE if all entries in column A are the same length and FALSE otherwise.

Upvotes: 1

Related Questions