[John]

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

I appreciate your help, -John

[Jacob Skaria]

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

[Harlan Grove[_2_]]

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)

[Jacob Skaria]

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)

[Rick Rothstein]

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)

[Harlan Grove[_2_]]

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

[Rick Rothstein]

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?