Extract one character

C

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
 
D

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
 
A

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'.
 
D

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.
 
C

Carl

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top