在第一个空白字符中插入vbNewLine

Question

我需要在第50个第一个字符之后遇到的第一个空白字符之后，在单元格字符串值中插入一个vbNewLine？

例如：“在公司，具备适当技能的员工有良好的晋升前景，那么就可以找到相关职位”。

在公司，具备适当技能的员工(vbNewLine)有很好的晋升前景，然后才能找到相关职位

Answer 1

替换函数可以从某个点开始，并使用通常留给默认值的参数替换单个字符。

Dim str As String, i As Long
i = 50
str = "At company employees with the right skills have good prospects to be promoted then a relevant position becomes available"
str = Left(str, i - 1) & Replace(str, Chr(32), Chr(10), _
                                 Start:=i, Count:=1)
Debug.Print str

以50为起点，我得到的结果是，

At company employees with the right skills have good
prospects to be promoted then a relevant position becomes available

Answer 2

既然你的问题提出得很好，这里有个解决办法

Sub solution()

    Dim test As String
    Dim pos As Integer

    test = "At company employees with the right skills have good prospects to be promoted then a relevant position becomes available"

    pos = InStr(51, test, " ") 'search for a space on or after the 51st character

    If (pos >= 51) Then
        'found a space
        test = Left(test, pos) & vbNewLine & Mid(test, pos + 1) 'miss out that space
    End If

    Debug.Print test


End Sub