CODEWITHSUNDEEP
excelvbaexcel-formula
Darren Jansen
Darren Jansen

Reputation: 25

Excel formula to find the longest string separated by characters

I have lots of strings separated by pipes in a column in Excel. None of the strings separated by pipes are allowed to be longer than 15 characters, so I want to find out if any cells have a string separated by pipes of more than 15 characters.

So in the example below, let's say that that is A2,A3,and A4. The formula will be in column B. the Formula in the B4 should alert me that A4 breaks the rule.

Bobby|Janet
Ted|Sammy|Susan|Fred
Billy|Maddie|JohnJacobJingleheimerShmitt|Matthew

Ideally, this formula should give me the actual number of characters in the longest string.

(Perhaps I need to create a custom formula with VBA?)

Upvotes: 0

Views: 611

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

If you have Excel 2013+ (and not Excel for MAC), you can use the FILTERXML function to split the string on the pipe into an array of results.

Then use the LEN function to get the length of each element Finally use either AGGREGATE normally entered to return the length of the longest, or use the MAX function in an array formula for the same result.

=AGGREGATE(14,4,LEN(FILTERXML("<t><s>" & SUBSTITUTE(A1,"|", "</s><s>") & "</s></t>", "//s")),1)

=MAX(LEN(FILTERXML("<t><s>" & SUBSTITUTE(A1,"|", "</s><s>") & "</s></t>", "//s")))

(To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.)

enter image description here

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152650

If you have Office 365 Excel then use this array formula:

=TEXTJOIN(",",TRUE,IF(LEN(TRIM(MID(SUBSTITUTE(A4,"|",REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A4)-LEN(SUBSTITUTE(A4,"|",""))+1))-1)*99+1,99)))>15,TRIM(MID(SUBSTITUTE(A4,"|",REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A4)-LEN(SUBSTITUTE(A4,"|",""))+1))-1)*99+1,99)) & " has " & LEN(TRIM(MID(SUBSTITUTE(A4,"|",REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A4)-LEN(SUBSTITUTE(A4,"|",""))+1))-1)*99+1,99))) & " Characters.",""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

With out Office 365 we can return a canned return but not the actual length

=IF(OR(LEN(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,"|",""))+1))-1)*99+1,99)))>15),"This cell has an entry with too many Characters","")

This is still an array formula, it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 5

urdearboy
urdearboy

Reputation: 14590

If you are open to VBA Solution...

Option Explicit

Public Function LenTest(Target As Range) As String

Dim Temp: Temp = Split(Target, "|")
Dim i As Long

For i = LBound(Temp) To UBound(Temp)
    If Len(Temp(i)) > 15 Then
        LenTest = "Fail @ " & Temp(i)
        Exit Function
    End If
Next i

LenTest = "Pass"

End Function

To implement, Open VBE > Insert Module > Paste Code

Then you can just call the function from your worksheet like any other function and select the cell that you want to test like so: =LenTest(A1) which will return Pass or Fail @ with the string that fails (I.E. char length > 15)

Upvotes: 3

Related Questions