Excel 2007 - Copy last word in a cell to another cell 1

[jeffm777]

I have a string of data in cell A1...

XXX.AD1212,super,6ft,black

I need a formula to move the last word in this string to cell B1.

Here's the kicker...sometimes there will be commas separating the data in the string, sometimes it will be spaces, and the number of commas and spaces will vary in the strings...I just need the last word in the string. I'm seeing an occasional period after the last word if that matters.

Thanks in advance for any help!

 

[SkipVought]

hi,

Paste this function code into a MODULE in the VB Editor (alt+F11) and use as you would any other spreadsheet function on your sheet.

Function LastWord(s As String)
    LastWord = Split(s, ",")(UBound(Split(s, ",")))
    LastWord = Split(LastWord, " ")(UBound(Split(s, " ")))
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffm777]

Awesome! Thanks - works like a charm.