Home » Blogs » Vishesh's blog

Extract Nth Word from a String - VBA

Vishesh's picture
Submitted by Vishesh on 19 July, 2010 - 05:29
Here is a VBA function to Extract the Nth Word from a String: 
Function GetNthWord(strStringFrom As String, strSplitCharacter As String, intExctractWordNumber As Integer) As String
    On Error Resume Next
    GetNthWord = VBA.Split(strStringFrom, strSplitCharacter)(intExctractWordNumber - 1)
    If Err.Number <> 0 Then
        GetNthWord = ""
    End If
    On Error GoTo 0
End Function
 
Sub ExecuteNthWord()
    MsgBox GetNthWord("Hi:Hello:Nick", ":", 2)
End Sub
»

Extract Nth Word in String - Formula

Submitted by Anonymous on 20 September, 2011 - 00:08.

(Sorry, I shouldn't have used angled brackets around WORDNBR as a symbol - stripped out of previous comment due to HTML. I've changed to B1 below.)

If anyone wants an in-cell solution here's a formula that uses SUBSTITUTE to replace the space before the nth word with a special character (DEL, char(127)) to then FIND it and the next word (i.e. end of nth word) for parsing:

=IFERROR(MID(" "&TRIM(A1),FIND(CHAR(127),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(127),B1))+1,IFERROR(FIND(char(127),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(127),B1+1))-FIND(CHAR(127),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(127),B1))-1,999)),"")

Where cell A1 = multi-word string
Change B1 to number of word required or cell reference if extracting words into separate cells.
Returns "" (null string) if nth word is not present (i.e. beyond range) - surrounding IFERROR().
Others I've seen (MID(MID(MID(SUBSTITUTE()),FIND())))) don't handle first and last words.

»

Extract Nth Word from a String - Formula

Submitted by Anonymous on 19 September, 2011 - 23:57.

If anyone wants an in-cell solution here's a formula that uses SUBSTITUTE to replace the space before the nth word with a special character (DEL, char(127)) to then FIND it and the next word (i.e. end of nth word) for parsing:

=IFERROR(MID(" "&TRIM(A1),FIND(CHAR(127),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(127),))+1,IFERROR(FIND(char(127),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(127),+1))-FIND(CHAR(127),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(127),))-1,999)),"")

Where cell A1 = multi-word string
Change to number number of word required or cell reference
Returns "" (null string) if nth word is not present (i.e. beyond range) - surrounding IFERROR().
Others I've seen (MID(MID(MID(SUBSTITUTE()),FIND())))) don't handle first and last words.

»