Extract one character

[Carl]

Column C in one worksheet has text strings, from Row 2 thru Row 676. These
strings can be two, three, or four words. I need a formula to extract the
first character to the right of the last space.
For example,

Rts is unpaid -> u
Ldpq to be quoted -> q
Will call -> c

Thanks for the help

 

[Dave Peterson]

One way is to use a helper column and a formula like this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),1)
and drag down

 

[Archimedes' Lever]

One way is to use a helper column and a formula like this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),1)
and drag down

One way is to set a cell to a particular value based on the contents of
that cell without testing a particular portion of a string's contents.

In other words, test for the entire string value, and set the value of
another cell based on that, then pass that cell's value.

Instead of trying to extract the 'c' from 'will call', look for the
entire string 'will call' and set the value of a cell to 'c'.

 

[Carl]

Thank you, works like a charm.
Now I need to figure out how it does the job???

 

[Dave Peterson]

if c2 holds
abc def
then
=SUBSTITUTE(C2," ",REPT(" ",99))
would look like:
abc(99 space characters)def

So
=RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99))
would give the right most 99 characters:
(96 space characters)def
(96 since def is just 3 characters)

=trim() removes the leading/trailing (and duplicated internal spaces!) in the
string:

so
=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99))
is just
def

and
=left(..., 1)
returns the first character of that def (just d).

I didn't come up with that =substitute() trick, but it works very nice under
certain conditions.

 

[Carl]

Thank you very much for the time spent in explaining the solution.
As always you efforts are greatly appreciated.