字符串长度基于长度超过3行 [英] String length over 3 lines based on length

问题描述

因此，我尝试通过VBA在excel中通过VBA将字符串拆分为3行(如果超过一定长度).我有2行的工作，但如果超过55个字符，则会与输出文件中的其他文本重叠.我希望它是动态的，因此它可以应付所有情况，即10到60个字符，并相应地分成几行.有什么建议吗?

So I'm trying to split a string via VBA in excel over 3 lines if it is over a certain length. I have it working for 2 lines but if it is over ~55 characters it overlaps onto other text in the ouput file. I want it dynamic so it copes with all cases i.e 10 to 60 characters , splitting up into lines accordingly. Any suggestions ?

 If Len(StrText) > 19 Then
           If Mid(StrText, 11, 1) = " " Then
              StrText = Left(StrText, 19) & Chr(10) & Chr(32) & Right(StrText, Len(StrText) - 19)
    Else
        m = 19
        Do
            m = m + 1
        Loop Until ((Mid(StrText, m, 1) = " ") Or (m = Len(StrText)))
        StrText = Left(StrText, m) & Chr(10) & Chr(32) & Right(StrText, Len(StrText) - m)
    End If
End If

推荐答案

下面的UDF应该可以为您做到这一点.这里的假设是您的字符串中有空格..如果没有，UDF很可能会引发意外结果(我没有考虑那种情况)

Below UDF should be able to do that for you. Assumption here is that your string has spaces.. if it doesn't, UDF will most likely throw unexpected results (I haven't catered for that scenario)

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

我在所需的字符数之前添加了对 SPACE 字符的检查，以防万一字符计数后没有空格.您可以根据需要将其取出

要使用UDF，只需用您的字符串调用它，它将返回预期的字符串

I added a check for SPACE character before the number of characters needed as well just in case there isn't a space after the character count. You can take this out if you want

To use the UDF, just call it with your string and it will return the expected string

如果传递给UDF的字符串中已经包含 LineFeed 字符，则UDF返回的行数将超过您期望的3行

If the string passed to the UDF already has a LineFeed character in it, UDF will return more than the 3 lines you expect

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