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 !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
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))

--
Biff
Microsoft Excel MVP


"dk" wrote in message
...
There is only 1 problem that it gives the word VALUE were there is less
then
2 words

"RagDyer" wrote:

"Easiest" is a relative term.

Here's a text formula.

With data in Column A, try this in B1, and copy down as needed:

=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"dk" wrote in message
...
the easiest possible

"carlo" wrote:

Hi dk

do you want to do that by formula or by programming?

cheers
Carlo

On Jan 11, 9:40 am, dk wrote:
How can we delete the last word in a cell from a whole column?