I forgot to add this tagline...

Of course, doing this squeezes out the multiple internal spaces, which may not be what the OP would want.

Rick


"Rick Rothstein (MVP - VB)" wrote in message ...
I think your formula needs to be this...

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

otherwise it will return the wrong answer if A1 contains multiple internal blank spaces, such as like the following...

one two three four

Rick


"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?