vba - 使用vba在excel单元格中拆分文本和数字

Question

我有一个单元格文本“12-3 0000 9 FLY AIR Make MY Trip”
我想要像 12-3 0000 9 这样的输出进入下一个单元格和下一个序列单元格“FLY AIR Make MY Trip”。

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
    'Updateby20150306
    Dim xLen As Long
    Dim xStr As String

    xLen = VBA.Len(pWorkRng.Value)

    For i = 1 To xLen
        xStr = VBA.Mid(pWorkRng.Value, i, 1)
        If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
            SplitText = SplitText + xStr
        End If
    Next
End Function

=SplitText(A2,FALSE)进入空白单元格以获取唯一的文本。

结果为 12300009
=SplitText(A2,TRUE)进入一个空白单元格以获取唯一的数字。

结果为“-FLY AIR Make MY Trip”

Answer 1

假设您想要获取数字的第一部分，如果您的参数 pIsNumber 设置为 true:

Option Explicit
Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Declare ALL variables
Dim xLen As Long
Dim xStr As String
Dim i As Integer
xLen = VBA.Len(pWorkRng.value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.value, i, 1)
     If pIsNumber And InStr(" -0123456789", xStr) Then
        SplitText = SplitText & xStr   ' use ampersand "&" instead of "+"

     ElseIf Not pIsNumber And InStr("-123456789", xStr) = 0 Then
        SplitText = SplitText & xStr
     End If
Next
If pIsNumber Then SplitText = Replace(SplitText, String(3, " "), " ")
End Function

注意

使用与号“&”而不是“+”来连接字符串值。顺便说一句，不要忘记声明变量并始终在代码模块的声明头中使用 Option Explicit。

祝你好运。