Excel-VBA, How do i extract nth word from some rows of sentences

Question

How do i extract nth word from some rows of sentences? For example, i have a sentence in column A1, and another in A2, A3 and so on, How do i extract 2nd word from each sentence? And display in a cell beside each sentence. Appreciated if someone can write an example for this, i'm new to excel-vba.

Answer 1

Split it;

'//get value
dim para As string: para = range("a1").value
'//normalize new lines to space & split on space
dim words() As string
words = Split(Replace$(para, vbCrLf, " "), " ")
'//3rd word in adjacent cell;
range("b1").value=words(2)

Answer 2

You can do this easily in VBA, but I am assuming you want to do this with Formulas.

Here is how you can break this down. Consider cell A1 as having the string you want to extract the second word from. You can use the Find function to determine where the second word starts from:

=FIND(" ", A1, 1)

Same function can be used to find out where the second word finishes:

=FIND(" ",A1, FIND(" ",A1)+1)

Now, we can use the Mid function in order to extract the word from its start and end positions:

=MID(A1, FIND(" ", A1, 1), FIND(" ",A1, FIND(" ",A1)+1)-FIND(" ", A1, 1))

This final formula is what you want to use. It looks complicated, but it is just the first two formulas copied into the Mid function.

I hope this helps.

Answer 3

You can paste this into a VBA module for a UDF, only works for a single cell though. Just give it a cell reference and which word number you want it to extract:

Function WordExtract(CellRef As Range, Wordnum As Integer)

    Dim StartPos As Integer, EndPos As Integer, Counter As Integer

    StartPos = 1

    For i = 1 To Len(CellRef)
        If Mid(CellRef, i, 1) = " " Then
            If Wordnum - Counter = 1 Then
                EndPos = i - StartPos
                Exit For
                Else:
                StartPos = i + 1
                Counter = Counter + 1
            End If
        End If
    Next i

    If EndPos = 0 Then EndPos = Len(CellRef)

    WordExtract = Mid(CellRef, StartPos, EndPos)

End Function