Search code examples
excelcellstring-lengthvba

Check if one cell of several cells on a row in Excel has length 30

I have several rows within each row several cells and I want to check if one of these cells in a row has length 30.

I tried using the LEN function, but the problem is that I have to check several cells in the row at one moment.

Solution

  • Excel treats TRUE as 1 and FALSE as 0. If you get an array of TRUE/FALSE values from comparing each cell in a row for a length of 30 and take the maximum, you will know if at least one cell has a length of 30.

    To check A2:G2 for any cell that has an untrimmed length of 30 characters/digits put this in H2,

    =AND(MAX(INDEX(--(LEN(A2:G2)=30),,)))

    Fill down for subsequent rows.

    To retrieve the first value that is 30 characters/digits in length put this into I2,

    =IFERROR(INDEX($A2:$G2, , SMALL(INDEX(COLUMN($A:$G)+(LEN($A2:$G2)<>30)*1E+99, , ), COLUMN(A:A))), "")

    Fill right for possible second, third, etc. values that are 30 characters wide. Fill down as necessary to catch subsequent rows.