Separate numbers and words

Question

Separate numbers and words

Navigation

#1 by (1 votes)

0

I have a database in excel where in the row / column I have dates and words. Also, with dates before 1900. And, in some instances, only the year with 4 digits.

Example:

"A1" 20-07-1882 Daniel

or

"A2" 1882 João

I use, to separate the numbers of 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 by using the "TRUE" parameter. However, in the case of the date in the short form, dd-mm-yyyy it removes the dash that separates the date ("-"), thus giving the result:

20071882

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

excel-vba

asked by anonymous 11.07.2018 / 09:19

1 answer

Buy list values Counter zeroing for no reason

score 1 · Accepted Answer

IsNumeric function

The IsNumeric () function 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 verify this, where step by step can be viewed with the F8 key and enabling the immediate scan 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 whether the value is numeric or if it is 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 Text Split in Excel has an alternative way of solving your problem.

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