remove last word

[dk]

How can we delete the last word in a cell from a whole column?

 

[carlo]

Hi dk

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

cheers
Carlo

 

[dk]

the easiest possible

Hi dk

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

cheers
Carlo

 

[RagDyer]

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

 

[dk]

There is only 1 problem that it gives the word VALUE were there is less then
2 words

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[RagDyeR]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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