Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

OK, try this code:

Function getWord(BigString As String, numOfWord As Integer, Optional Separator As String) As String
Dim LocList() as String
Dim i%, cnt%
    getWord = ""
    If IsMissing(Separator) Then Separator = " "
    LocList = Split(BigString,Separator)
    If (numOfWord = 0) Or (Abs(numOfWord) > UBound(LocList())) Then Exit Function
    cnt = 0
    If numOfWord > 0 Then
        For i = LBound(LocList()) To UBound(LocList())
            If LocList(i) <> "" Then
                cnt = cnt + 1
                If cnt = numOfWord Then
                    getWord = LocList(i)
                    Exit Function
                EndIf
            EndIf
        Next i
    Else        
        For i = UBound(LocList()) To LBound(LocList()) Step -1
            If LocList(i) <> "" Then
                cnt = cnt - 1
                If cnt = numOfWord Then
                    getWord = LocList(i)
                    Exit Function
                EndIf
            EndIf
        Next i
    EndIf
End Function

With negative second parameter you can get word from end of phrase - at your example =GETWORD(A1; -3) is on

OK, try this code:

Function getWord(BigString As String, numOfWord As Integer, Optional Separator As String) As String
Dim LocList() as String
Dim i%, cnt%
    getWord = ""
    If IsMissing(Separator) Then Separator = " "
    LocList = Split(BigString,Separator)
    If (numOfWord = 0) Or (Abs(numOfWord) > UBound(LocList())) Then Exit Function
    cnt = 0
    If numOfWord > 0 Then
        For i = LBound(LocList()) To UBound(LocList())
            If LocList(i) <> "" Then
                cnt = cnt + 1
                If cnt = numOfWord Then
                    getWord = LocList(i)
                    Exit Function
                EndIf
            EndIf
        Next i
    Else        
        For i = UBound(LocList()) To LBound(LocList()) Step -1
            If LocList(i) <> "" Then
                cnt = cnt - 1
                If cnt = numOfWord Then
                    getWord = LocList(i)
                    Exit Function
                EndIf
            EndIf
        Next i
    EndIf
End Function

With negative second parameter you can get word from end of phrase - at your example example

=GETWORD(A1; -3) is on

OK, try this code:

Function getWord(BigString As String, numOfWord As Integer, Optional Separator As String) As String
Dim LocList() as String
Dim i%, cnt%
    getWord = ""
    If IsMissing(Separator) Then Separator = " "
    LocList = Split(BigString,Separator)
    If (numOfWord = 0) Or (Abs(numOfWord) > UBound(LocList())) UBound(LocList())+1) Then Exit Function
    cnt = 0
    If numOfWord > 0 Then
        For i = LBound(LocList()) To UBound(LocList())
            If LocList(i) <> "" Then
                cnt = cnt + 1
                If cnt = numOfWord Then
                    getWord = LocList(i)
                    Exit Function
                EndIf
            EndIf
        Next i
    Else        
        For i = UBound(LocList()) To LBound(LocList()) Step -1
            If LocList(i) <> "" Then
                cnt = cnt - 1
                If cnt = numOfWord Then
                    getWord = LocList(i)
                    Exit Function
                EndIf
            EndIf
        Next i
    EndIf
End Function

With negative second parameter you can get word from end of phrase - at your example

=GETWORD(A1; -3) is on