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

aditee
aditee used Ask the Experts™
on
Hi,
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

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
    Next

Set regex = Nothing
End Sub

Open in new window

REgards
Bill PrewTest your restores, not your backups...
Top Expert 2016
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)
        Else
            ExtractWord = ""
        End If
    Else
        ExtractWord = a(0)
    End If
End Function

Open in new window


»bp
Top Expert 2016

Commented:
Since we don't know the separator, the regex solution works for more possibilities

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial