And, this will return the *single* word:
=IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"
",REPT(" ",99)),99)))-1))
--
Regards,
RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------
If a cell contains only one word this modified version of RD's formula will
"remove" it (return a blank cell):
=TRIM(LEFT(A1,LEN(TRIM(A1))+1-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT("
",99)),99)))-1))