How to extract the first word from the cell in a column nd paste it in another column using vba.

I know how to extract the first from a cell in a column and paste in another column using vba. But what i want is if the first text is a number then 2nd text should get extracted. Please help me out with the problem.
Who is Participating?
Rgonzo1971Connect With a Mentor Commented:

pls try
Sub CellRegexSimple()
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "\b[a-z]+\b"
    End With
    For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
        Set res = regex.Execute(c.Text)
        c.Offset(, 1) = res.Item(0).Value

Set regex = Nothing
End Sub

Open in new window

Bill PrewConnect With a Mentor Commented:
This should work:

Function ExtractWord(strText As String) As String
    If strText = "" Then
        ExtractWord = ""
        Exit Function
    End If
    a = Split(strText, " ")
    If IsNumeric(a(0)) Then
        If UBound(a) > 0 Then
            ExtractWord = a(1)
            ExtractWord = ""
        End If
        ExtractWord = a(0)
    End If
End Function

Open in new window

Since we don't know the separator, the regex solution works for more possibilities
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.