CODEWITHSUNDEEP
excelvba
tlewis3348
tlewis3348

Reputation: 486

How can I get the length of the longest string in a column (Excel)?

I have an Excel spreadsheet that I am writing out to a file. I am currently using the following method to reproduce the contents of each cell:

cell_contents = Right(Space(10) & Trim(Cells(iRow, iColumn)), 10)

However, if the contents of the cell are longer than 10 characters long (or however long I choose to specify), then I loose parts of the data. Is there a way to quickly and easily get the length of the longest string in a range of cells?

I have seen people suggest using the following loop, but I was hoping that I might be able to do it without having to loop over all the cells:

For Each c In SourceRange.Cells
   if len(c) > b then b = len(c)
Next c

Upvotes: 10

Views: 29445

Answers (4)

Sim2K
Sim2K

Reputation: 77

Could just use ... {=MAX(LEN(A2:A200))}

In the top cell put =MAX(LEN(A2:A200)) .. and then press CTRL-SHIFT-ENTER to get the curly braces.

Upvotes: 1

Leadfingers
Leadfingers

Reputation: 11

Suppose that the data is in column A and there is a heading row for your spreadsheet.

  1. Create a new column next to it and enter in the formula: =LEN(A2)
  2. Autofill down that formula.
  3. Turn on filtering for that column and the click the drop-down box on the column heading cell.
  4. Scroll down to the bottom of the list where the largest summarized value is.
    That will be the largest value in the column.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Record these steps as a macro in the cell you want to calculate the max length.

1) enter this formula

=MAX(LEN(A1:A4), 1) -- Edit for your range.

2) press control-shift enter (FormulaArray)

3) now stop recording macro

4) view the VBA. copy what you need to your VBA.

Should give you something like this:

Selection.FormulaArray = "=MAX(LEN(R[-10]C[1]:R[-1]C[1]))"

Upvotes: 11

dedek
dedek

Reputation: 8301

Press Alt-F11, insert new module, paste code bellow.

Public Function maxRangeLength(data As Range) As Integer
    Dim ret As Integer

    ret = 0

    For Each cell In data
        ret = Application.Max(ret, Len(cell))
    Next cell

    maxRangeLength = ret
End Function

Usage:

=maxRangeLength(A8:D11)

Upvotes: 7

Related Questions