remove last word

  • Thread starter Thread starter dk
  • Start date Start date
"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)
 
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))
 
The "easiest possible" is a matter of interpretation. You already have a formula solution, so here is a macro solution for you to consider.

Sub RemoveLastWord()
Dim R As Range
Dim LastRow As Long
Dim Words() As String
Const Col As String = "A"
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For Each R In Range(Col & "1:" & Col & CStr(LastRow))
Words = Split(RTrim$(R.Value))
Words(UBound(Words)) = ""
R.Value = RTrim$(Join(Words))
Next
End Sub

Put the above subroutine in the code window for the sheet you want it to apply to and change the letter designation for the column to apply it to (assigned to the Const Col) to the column letter designation you want. One note, after this subroutine is run, there will be no trailing spaces in the cell even if the last word was separated from the rest of the text by multiple spaces or if the last word was followed by one or more spaces; however, all other multiple spaces in the text will be preserved.

Rick
 
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))
 
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
 
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


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
 
Back
Top