Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
Is there a way to pull just the last word from a text string in Excel?
I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
Try this:
For text in A1 This formula returns the word after the last space in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )) Note: if there are no spaces the formula returns #N/A. This formula allows for no-spaces contents B1: =IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )),A1) Does that help? *********** Regards, Ron XL2002, WinXP "nmp" wrote: Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
On Mon, 25 Sep 2006 12:08:01 -0700, nmp wrote:
Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
Ron,
I'm not sure what to change on the INDEX functions to match my data. My data starts in cell E2 and I have 1586 rows, so I want to be able to copy the formula all the way down. I'll start the formula in F2. There are spaces in each cell of data I want to extract the last word from. Thanks! "Ron Coderre" wrote: Try this: For text in A1 This formula returns the word after the last space in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )) Note: if there are no spaces the formula returns #N/A. This formula allows for no-spaces contents B1: =IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )),A1) Does that help? *********** Regards, Ron XL2002, WinXP "nmp" wrote: Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
You wouldn't need to alter the INDEX portions of the formula.
For a string in E2 that may or may not contain a space F2: =IF(COUNTIF(E2,"* *"),RIGHT(E2,LEN(E2)-LOOKUP(LEN(E2),FIND(" ",E2,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(E2),1))) )),E2) Actually, Ron Rosenfelds formula may be easier to decipher. If there may be no spaces in the string, just alter it to this: F2: =IF(COUNTIF(E2,"* *"),MID(E2,FIND(CHAR(1),SUBSTITUTE(E2," ",CHAR(1),LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))+1,255),E2) Does that help? *********** Regards, Ron XL2002, WinXP "nmp" wrote: Ron, I'm not sure what to change on the INDEX functions to match my data. My data starts in cell E2 and I have 1586 rows, so I want to be able to copy the formula all the way down. I'll start the formula in F2. There are spaces in each cell of data I want to extract the last word from. Thanks! "Ron Coderre" wrote: Try this: For text in A1 This formula returns the word after the last space in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )) Note: if there are no spaces the formula returns #N/A. This formula allows for no-spaces contents B1: =IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )),A1) Does that help? *********** Regards, Ron XL2002, WinXP "nmp" wrote: Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
That worked!
Thank you! "Ron Rosenfeld" wrote: On Mon, 25 Sep 2006 12:08:01 -0700, nmp wrote: Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
On Mon, 25 Sep 2006 13:16:02 -0700, nmp wrote:
That worked! Thank you! You're welcome. Note that if there are no spaces in the string, the formula will return an error. If that needs to be avoided, use this instead: =IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),A1,MID( A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN( A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)) --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
Here's another one:
=RIGHT(A5,LEN(" "&A5)-LOOKUP(LEN(" "&A5),FIND(" "," "&A5,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A5),1))) )) "Error trap" built-in. Biff "nmp" wrote in message ... Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull last word from a text string in Excel
Oh, I see Ron C has already posted pretty much the same thing.
Biff "Biff" wrote in message ... Here's another one: =RIGHT(A5,LEN(" "&A5)-LOOKUP(LEN(" "&A5),FIND(" "," "&A5,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A5),1))) )) "Error trap" built-in. Biff "nmp" wrote in message ... Is there a way to pull just the last word from a text string in Excel? I have a sheet with a column of text descriptions that are various lengths and the last words in each cell are various lengths. Basically I want everything to the right of the last space. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
highlight the word when excel finds text you are searching for | Excel Worksheet Functions | |||
How can delete the duplicate word from a string in cell in EXCEL | Excel Discussion (Misc queries) | |||
Excel - Find & Replace text in a string | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |