EXCEL VBA String length over 3 lines based on leng

Question

Based on the code Zac gave me in

String length over 3 lines based on length

I now need to update to it to include the possibility that the text/words may not be separated by spaces but also the characters "/" or "_". I've had a good fiddle without success. I added if statements to the function such as :-

If InStr(55, sText, "_") > 0 Then
        sGreater55 = Mid(sText, InStr(55, sText, "_"))

Without success :-(

Function SetString(ByVal sText As String) As String
    Dim sGreater55$, sGreater19$

' If text is greater than 55 characters, first lets capture the string after 55 characters (depending on where the SPACE character is)

If Len(sText) > 55 Then
    If InStr(55, sText, " ") > 0 Then
        sGreater55 = Mid(sText, InStr(55, sText, " "))
    Else
        sGreater55 = Mid(sText, InStrRev(sText, " ", 55))
    End If
End If

' If text is greater than 19 characters, lets build the string after 19 characters (depending on where the SPACE character is)

If Len(sText) > 19 Then
    If InStr(19, sText, " ") > 0 Then
        sGreater19 = Mid(sText, InStr(19, sText, " "))
    Else
        sGreater19 = Mid(sText, InStrRev(sText, " ", 19))
    End If
    sGreater19 = Left(sGreater19, Len(sGreater19) - Len(sGreater55))
End If

' Now lets build the complete string

SetString = Left(sText, Len(sText) - (Len(sGreater19) + Len(sGreater55))) & vbLf & sGreater19 & vbLf & sGreater55

End Function

Answer 1

Assuming that there might be an mixture of the split characters (Eg blah_blah_blah blah_blah/blahblah: Create a copy of your input string, replace all possible split characters by blank and slighly modify the function:

Function SetString(ByVal sText As String) As String
    Dim sGreater55$, sGreater19$
    Dim sTextCopy As String
    sTextCopy = Replace(Replace(sText, "/", " "), "_", " ")

    ' If text is greater than 55 characters, first lets capture the string after 55 characters (depending on where the SPACE character is)
    If Len(sText) > 55 Then
        If InStr(55, sText, " ") > 0 Then
            sGreater55 = Mid(sText, InStr(55, sTextCopy, " "))
        Else
            sGreater55 = Mid(sText, InStrRev(sTextCopy, " ", 55))
        End If
    End If

    ' If text is greater than 19 characters, lets build the string after 19 characters (depending on where the SPACE character is)
    If Len(sText) > 19 Then
        If InStr(19, sText, " ") > 0 Then
            sGreater19 = Mid(sText, InStr(19, sTextCopy, " "))
        Else
            sGreater19 = Mid(sText, InStrRev(sTextCopy, " ", 19))
        End If
        sGreater19 = Left(sGreater19, Len(sGreater19) - Len(sGreater55))
    End If

    ' Now lets build the complete string
    SetString = Left(sText, Len(sText) - (Len(sGreater19) + Len(sGreater55))) & vbLf & sGreater19 & vbLf & sGreater55

End Function

Answer 2

Firstly Assumptions:

• Text provided to UDF will have one of the 3 "dividers" separating text (i.e. text is divided by a space or "_" or "/"). Provided text is not separated by a combination of the "dividers" (i.e. don't have text like blah blah blah\blah\blah)
• If non of the 3 "dividers" exist in the provided text, empty string is returned

Based on the above assumptions, try this:

Function SetString(ByVal sText As String) As String
    Dim sGreater55$, sGreater19$
    Dim sChrToCheck As String: sChrToCheck = ""

    ' First lets decide which character we need to use to seperate string
    If InStr(1, sText, " ") > 0 Then
        sChrToCheck = " "
    ElseIf InStr(1, sText, "_") > 0 Then
        sChrToCheck = "_"
    ElseIf InStr(1, sText, "/") > 0 Then
        sChrToCheck = "/"
    Else
        ' Non of the expected text dividers found in the specified string to return an empty string
        Exit Function
    End If

    ' If text is greater than 55 characters, first lets capture the string after 55 characters (depending on where the SPACE character is)
    If Len(sText) > 55 Then
        If InStr(55, sText, sChrToCheck) > 0 Then
            sGreater55 = Mid(sText, InStr(55, sText, sChrToCheck))
        Else
            sGreater55 = Mid(sText, InStrRev(sText, sChrToCheck, 55))
        End If
    End If

    ' If text is greater than 19 characters, lets build the string after 19 characters (depending on where the SPACE character is)
    If Len(sText) > 19 Then
        If InStr(19, sText, " ") > 0 Then
            sGreater19 = Mid(sText, InStr(19, sText, sChrToCheck))
        Else
            sGreater19 = Mid(sText, InStrRev(sText, sChrToCheck, 19))
        End If
        sGreater19 = Left(sGreater19, Len(sGreater19) - Len(sGreater55))
    End If

    ' Now lets build the complete string
    SetString = Left(sText, Len(sText) - (Len(sGreater19) + Len(sGreater55))) & vbLf & sGreater19 & vbLf & sGreater55

End Function