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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.