The last word

  • Thread starter Thread starter John
  • Start date Start date
J

John

How do I extract the last word in a character string of words and spaces?

I appreciate your help, -John
 
With your name in A1 try

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

If this post helps click Yes
 
With your name in A1 try

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

If this post helps click Yes
 
Jacob Skaria said:
With your name in A1 try

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
Click to expand...
....

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)
 
Jacob Skaria said:
With your name in A1 try

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
Click to expand...
....

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)
 
A more robust approach is
=MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,32767)
Click to expand...

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)?
 
A more robust approach is
=MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,32767)
Click to expand...

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 Rothstein said:
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)?
Click to expand...
....

Showing my age. Ever used paper tape as a storage medium?
 
Rick Rothstein said:
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)?
Click to expand...
....

Showing my age. Ever used paper tape as a storage medium?
 
LOL -- Yes... many, many years ago (but only for a short time before moving
on to IBM "punch cards").
 
LOL -- Yes... many, many years ago (but only for a short time before moving
on to IBM "punch cards").
 
Back
Top