Extract one character
 

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

Extract one character
 

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



Carl wrote:

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

Extract one character
 

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

"Dave Peterson" wrote:

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



Carl wrote:

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

Extract one character
 

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 wrote:

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

"Dave Peterson" wrote:

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



Carl wrote:

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


--

Dave Peterson

Extract one character
 

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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



Carl wrote:

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


--

Dave Peterson