How to get the second last portion of different strings?

[shahin]

I've learnt lately how to get the last portion of a string applying Ubound method. If i wish to get the second last portion of any string, what should i do then? I'm pasting below few strings in which each contains state name of its second last portion of each string. I would like to get that.

Code I've written so far:

Sub splitting_items()
    Dim lrow As Long, cel As Range, item_list As Variant
    lrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    For Each cel In Sheet2.Range("A1:A" & lrow)
        item_list = Split(cel, " ")
        Debug.Print item_list(UBound(item_list))
    Next cel
End Sub

Strings I talked about:

121 Shute Harbour Road  CANNONVALE QLD 4802
104 Balwyn Road  BALWYN VIC 3103
61 Prospect Rd  CANLEY VALE NSW 2166
107 Lord Street  CABRAMATTA NSW 2166
88 Wolseley St  FAIRFIELD NSW 2165

From the above strings I wanna parse QLD, VIC, NSW etc.

 

[Marc L]

See How to customize a URL using IF statement or something? …
​

 

[shahin]

The thing is: If i want to count index from the first position then I will end up scraping wrong values. However, If i count from the last portion then the second last is always the value I want. In your provided link there was nothing discussed about scraping values using index from the reverse way or I could not get it perhaps.

 

[shahin]

Thanks Marc L, for the hint. Isn't this what you indirectly mean? It indeed gives me the values.

Sub splitting_items()
    Dim lrow As Long, cel As Range, item_list As Variant
    lrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    For Each cel In Sheet2.Range("A1:A" & lrow)
        item_list = Split(cel, " ")
        x = UBound(item_list)
        Debug.Print item_list(x - 1)
    Next cel
End Sub