| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
remove last word
How can we delete the last word in a cell from a whole column?
|
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to remove spaces from a word in a cell | Excel Discussion (Misc queries) | |||
| How do I remove the automatic help box in Word and Excel? | New Users to Excel | |||
| Remove complete row containing a single word | Excel Worksheet Functions | |||
| Remove the word 'total' from subtotals | Excel Discussion (Misc queries) | |||
| How to remove a word from dictionary? | Excel Discussion (Misc queries) | |||
Linear Mode
