Split text and numbers

[JohnExcel222]

Hi there, this code splits the text and numbers into 2 columns. How can I preserve the spaces between the numbers?

Example
If the string contains: Word 2007 Excel 2016...
I would like to get Word Excel in one column and 2007 2016 in another column (and not 20072016)

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String

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

 

[Joe4]

Try this adjustment to the code:

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String

    Dim xLen As Long
    Dim xStr As String
    Dim i As Long
    
    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
        If xStr = " " Then SplitText = SplitText + xStr
    Next i
    
End Function

 

[JohnExcel222]

Try this adjustment to the code:

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String

    Dim xLen As Long
    Dim xStr As String
    Dim i As Long
   
    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
        If xStr = " " Then SplitText = SplitText + xStr
    Next i
   
End Function

awesome happy Thank you !!

 

[Joe4]

You are welcome.
Glad I was able to help!