remove last word
 

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

remove last word
 

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?

remove last word
 

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?

remove last word
 

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

remove last word
 

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?

remove last word
 

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?

remove last word
 

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



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

remove last word
 

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?

remove last word
 

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?

remove last word
 

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?