Extract second word from text tring

[monkeyharris]

I've seen references to this on-line but nothing seems to work.
In column F from F2 i have various text strings IE: "CPI Beaconsfield", "Diverscite Pleinbois", "Hodge Clemco" etc etc... I want to extract the second word so in E2 onwards it reads Beaconsfield, Pleinbois, Clemco etc.......

 

[NeonRedSharpie]

=RIGHT(F2,LEN(F2)-FIND(" ",F2))

This assumes two words with a space. You could also just use text to columns with a space as delimiter.

 

[monkeyharris]

Sorry, maybe i should have been clearer, i want to extract ONLY the second word. This formula extracts the second word onwards.

 

[Rick Rothstein]

i want to extract ONLY the second word. This formula extracts the second word onwards.

Give this formula a try...

=MID(A1,FIND(" ",A1&" "),FIND(" ",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" "))

or, alternately, this one...

=TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",99)),100,99))

 

[monkeyharris]

Rick, the second one worked PERFECT, the first one extracted only the second word but had a space at the beginning. Many thanks.

 

[Rick Rothstein]

Rick, the second one worked PERFECT, the first one extracted only the second word but had a space at the beginning. Many thanks.

If you care, here is the fix for that first one..

=MID(A1,FIND(" ",A1&" ")+1,FIND(" ",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" ")-1)