Separate numbers and words

Question

I have a database in excel where in the row/column I have dates and words. Inclusive, with dates before 1900. And in some instances, only the four-digit year.

Example:

"A1" 20-07-1882 Daniel 

or

"A2" 1882 João

I use, to separate the numbers from the words, this function:

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

It works perfectly using the "TRUE" parameter. However, in the case of the date in summary form, dd-mm-yyyy it removes the trace that separates the date ("-") so the result:

20071882

The idea here is that the trace is not removed at the time of separation.

Answer 1

Isnumeric function

The function Isnumeric() is being used. Where the description is:

Returns a Boolean value that indicates whether an expression can be evaluated as a number.

Therefore, it only validates numbers and the hyphen is not included.

Test Code

This test code can be used to check this, where step by step can be seen with the key F8 and enabling the immediate verification window.

Sub teste()

    Dim xLen As Long
    Dim xStr As String
    pWorkRng = "20-07-1882 Daniel"
    pIsNumber = True
    xLen = VBA.Len(pWorkRng)
    For i = 1 To xLen
        xStr = VBA.Mid(pWorkRng, i, 1)
        Debug.Print xStr
        If ((VBA.IsNumeric(xStr) And pIsNumber)) _
             Or (Not (VBA.IsNumeric(xStr) And Not (pIsNumber))) Then
            Str_f = Str_f + xStr
            Debug.Print Str_f
        End If
    Next
End Sub

Solution

To solve the problem, the function must accept if the value is numerical or if it is a hyphen: (VBA.IsNumeric(xStr) Or xStr = "-"), but there can be no hyphen in the person’s name.

Code

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
        Debug.Print xStr
        xStr = VBA.Mid(pWorkRng.Value, i, 1)
        If ((VBA.IsNumeric(xStr) Or xStr = "-") And pIsNumber) _
            Or (Not (VBA.IsNumeric(xStr) Or xStr = "-") And _
                     Not (pIsNumber)) Then
            SplitText = SplitText + xStr
            Debug.Print SplitText
        End If
    Next
End Function

Alternative

The answer to the question Split text in excel has an alternative way of solving your problem.

Where the use of a separator, in case a space " ", can be used to split the string into elements and the desired element is chosen as output.