基于长度，空格，"/"或"EXCEL"的EXCEL VBA超过3行的字符串长度_"人物 [英] EXCEL VBA String length over 3 lines based on length , spaces, "/", or " _" characters

问题描述

基于Zac在

基于长度的超过3行的字符串长度

我现在需要对其进行更新，以包括以下可能性:文本/单词不能用空格分隔，而不能用字符"/"或"_"分隔.我的小提琴没有成功.我在函数中添加了if语句，例如:-

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, "_"))

没有成功:-(

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

'如果文本大于55个字符，则首先让我们捕获55个字符之后的字符串(取决于SPACE字符的位置)

' 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

'如果文本大于19个字符，则让我们在19个字符之后构建字符串(取决于SPACE字符的位置)

' 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

推荐答案

假定 split 字符可能混合在一起(例如， blah_blah_blah blah_blah/blahblah :创建输入字符串的副本，将所有可能的 split 字符替换为空白并轻松修改函数:

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

这篇关于基于长度，空格，"/"或"EXCEL"的EXCEL VBA超过3行的字符串长度_"人物的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！