get the penultimate word in a string

[jamesv]

Hello,

For example: "cheap games wii uk"

How would one get the penultimate word in a sentence (wii) via Microsoft Excel functions.

I have googled to find the solution, but I cannot find anything.

I think I will need a mid and a search, but I do not know how to put it all together.

Any help would be greatly appreciated.

 

[barry houdini]

Modifying a formula to get the last word......

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ", REPT(" ",100)),200),100))

 

[jamesv]

Just to fire back at you to see if I have understand, if I flip the RIGHT to a LEFT will that get the 2nd word instead of the penultimate?

 

[barry houdini]

The LEFT and RIGHT would need to be swapped, yes, then that would get word 2