|
The last word
How do I extract the last word in a character string of words and spaces?
I appreciate your help, -John |
The last word
With your name in A1 try
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) If this post helps click Yes --------------- Jacob Skaria "John" wrote: How do I extract the last word in a character string of words and spaces? I appreciate your help, -John |
The last word
Jacob Skaria wrote...
With your name in A1 try =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) .... If the string were long with many spaces, this could fail. If the string happens to end with trailing spaces, this WILL fail. The work-around for that is another TRIM call. =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) A more robust approach is =MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127), LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,32767) which assumes CHAR(127) wouldn't be found in the string. In the very unlikely chance it could, the most robust approach involves using a defined name like seq referring to the formula =ROW(INDEX(Incurred!$1:$65536,1,1):INDEX(Incurred! $1:$65536,32767,1)) and using it in the formula =MID(TRIM(A1),LOOKUP(2,1/(MID(" "&TRIM(A1),seq,1)=" "),seq),32767) |
The last word
Thanks Harlan
-- If this post helps click Yes --------------- Jacob Skaria "Harlan Grove" wrote: Jacob Skaria wrote... With your name in A1 try =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) .... If the string were long with many spaces, this could fail. If the string happens to end with trailing spaces, this WILL fail. The work-around for that is another TRIM call. =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) A more robust approach is =MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127), LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,32767) which assumes CHAR(127) wouldn't be found in the string. In the very unlikely chance it could, the most robust approach involves using a defined name like seq referring to the formula =ROW(INDEX(Incurred!$1:$65536,1,1):INDEX(Incurred! $1:$65536,32767,1)) and using it in the formula =MID(TRIM(A1),LOOKUP(2,1/(MID(" "&TRIM(A1),seq,1)=" "),seq),32767) |
The last word
A more robust approach is
=MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127), LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,32767) Just out of curiosity... any special reason for choosing CHAR(127) as opposed to using one of the rarely used sub-32 characters codes; for example, CHAR(1)? -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... Jacob Skaria wrote... With your name in A1 try =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) ... If the string were long with many spaces, this could fail. If the string happens to end with trailing spaces, this WILL fail. The work-around for that is another TRIM call. =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) A more robust approach is =MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127), LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,32767) which assumes CHAR(127) wouldn't be found in the string. In the very unlikely chance it could, the most robust approach involves using a defined name like seq referring to the formula =ROW(INDEX(Incurred!$1:$65536,1,1):INDEX(Incurred! $1:$65536,32767,1)) and using it in the formula =MID(TRIM(A1),LOOKUP(2,1/(MID(" "&TRIM(A1),seq,1)=" "),seq),32767) |
The last word
"Rick Rothstein" wrote...
.... Just out of curiosity... any special reason for choosing CHAR(127) as opposed to using one of the rarely used sub-32 characters codes; for example, CHAR(1)? .... Showing my age. Ever used paper tape as a storage medium? |
The last word
LOL -- Yes... many, many years ago (but only for a short time before moving
on to IBM "punch cards"). -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... "Rick Rothstein" wrote... ... Just out of curiosity... any special reason for choosing CHAR(127) as opposed to using one of the rarely used sub-32 characters codes; for example, CHAR(1)? ... Showing my age. Ever used paper tape as a storage medium? |
| All times are GMT +1. The time now is 10:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com