Need Excel formula to delete the last word in a cell

[jhillas]

Hello,

This seems so easy, but I can't get it to work correctly.
If a cell or group of cells contains a product description, and the last word is undesired in all the cells, how would we go about this? I've found this formula to delete the first word:

=RIGHT(A1,LEN(A1)-FIND(" ", A1,1))

before:
This is a test.

after
is a test.

What is desired is:
This is a

Thank you.

 

[Aladin Akyurek]

...

The formula in cell B1 is ...

=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

How does this differ from the one I posted?

 

[Yogi Anand]

...

The formula in cell B1 is ...

=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

How does this differ from the one I posted?

Hi Aladin:

Unless I am missing something here -- to delete the last word in the string 'This is a test.' -- your formulation results in ...

This

My formulation results in ...

This is a

 

[Aladin Akyurek]

...

The formula in cell B1 is ...

=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

How does this differ from the one I posted?

Hi Aladin:

Unless I am missing something here -- to delete the last word in the string 'This is a test.' -- your formulation results in ...

This

My formulation results in ...

This is a

I see there is an omission... It should be:

=TRIM(LEFT(A1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

 

[Aladin Akyurek]

=TRIM(LEFT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Corrected for an omission...

=TRIM(LEFT(A1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

 

[ashwinreddy]

Thank you . but in my case i have to delete last word only if the last complete word is in Upper case.

<tbody>
</tbody>