Here is a super convenient function that will return the length of the longest string in a single cell. This is useful when you have to make sure none of the lines in a cell are over a character limit (just apply conditional formating to the cell with this formula so that it turns red if it goes over a specific limit, etc.).
I had originally written this so that is just looped over each character and checked if it was a line break or not, but that would make the function run slower the larger the cells became (it already runs really fast, though).
I recently discovered that spliting cell into an array of sentences (with line break being the divider) and then just using LEN on each of them was much faster, and handles well even with long text. The result is simple: check if there is a line break in the cell (if not just return LEN), then split the cell into sentences. Then run LEN on each sentence, setting the longest length you come across as the maxLength. At the end, you have the length of the longest sentence! Simple, huh?
-----------------------------------------
Function StrMax(ByVal text As String) As Long
Application.ScreenUpdating = False
Dim maxLength As Long
Dim i As Long
Dim strings() As String
If InStr(text, vbLf) <> 0 Then
strings() = Split(text, vbLf)
For i = 0 To UBound(strings())
If Len(strings(i)) > maxLength Then
maxLength = Len(strings(i))
End If
Next
StrMax = maxLength
Else
StrMax = Len(text)
End If
Application.ScreenUpdating = True
End Function
-----------------------------------------
Link: