Another Text Function problem

[Tom James]

In case RD, Ken, or Barbara did not get my message, thanks for your help
with the last text function problem. Your examples and explanations were
very helpful!

Here's another problem. Here is a list of imported codes:

JMCC2C
KAO2D
JMCC2A
JMCC2E
RPT2A
RPT2B
RPT2C
RPT2E
PEC1C
PEC1D

The first few characters represent the description, the number (second from
the end) represent a region, and the last number represents the
manufacturer. The manufacturer column is the easy one. But I am trying to
figure out how to extract the description code (which can vary between 1 to
4 characters, and the region code.

 

[Bob Phillips]

=LEFT(A1,LEN(A1)-2)

=MID(A1,LEN(A1)-1,1)

 

[Leo Heuser]

In case RD, Ken, or Barbara did not get my message, thanks for your help
with the last text function problem. Your examples and explanations were
very helpful!

Here's another problem. Here is a list of imported codes:

JMCC2C
KAO2D
JMCC2A
JMCC2E
RPT2A
RPT2B
RPT2C
RPT2E
PEC1C
PEC1D

The first few characters represent the description, the number (second
from the end) represent a region, and the last number represents the
manufacturer. The manufacturer column is the easy one. But I am trying to
figure out how to extract the description code (which can vary between 1
to 4 characters, and the region code.

Tom

Description code:

=LEFT(A1,LEN(A1)-2)

Region code:

=MID(A1,LEN(A1)-1,1)

 

[RagDyer]

For the description, try this:

=LEFT(A1,LEN(A1)-2)

And the region:

=LEFT(RIGHT(A1,2))

 

[Tom James]

Thanks again to Bob, Leo, and RD. I have not used a lot of text functions in
the past and I need to get a better understanding how the LEN function works
with the other text functions. Your examples have given me the start I
needed.

Tom

 

[Tom James]

Hi Bob,

Thanks for your response. Both the solutions you provided worked, but I am
not sure why the second one works:

=MID(A1,LEN(A1)-1,1)

If the reference cell A1 contains COOP1C, I would think your formula would
return a "C" not "1", which is the correct result.

Here's my thinking (please tell me why I am wrong): The first argument, A1,
references COOP1C. The second argument, LEN(A1)-1, would return COOP1, which
is the word from which I want to extract my character. The third argument is
1, the number of characters I want returned. But doesn't Excel evaluate the
second argument, COOP1, left to right? So, wouldn't it return a "C" instead
of a "1"? I understand that it is returning the correct answer, but I don't
know why.

 

[Tom James]

Hi Leo,

Thanks for your response. I put the following question to Bob also, since he
provided the same solution.

Both the solutions you provided worked, but I am not sure why the second one
works:

=MID(A1,LEN(A1)-1,1)

If the reference cell A1 contains COOP1C, I would think your formula would
return a "C" not "1", which is the correct result.

Here's my thinking (please tell me why I am wrong): The first argument, A1,
references COOP1C. The second argument, LEN(A1)-1, would return COOP1, which
is the word from which I want to extract my character. The third argument is
1, the number of characters I want returned. But doesn't Excel evaluate the
second argument, COOP1, left to right? So, wouldn't it return a "C" instead
of a "1"? I understand that it is returning the correct answer, but I don't
know why.

Thanks!

Tom

 

[Tom James]

Hi RD,

I must say, that second example of yours (the one that returns the reason)
is interesting. I had to stare at it for a few minutes until I figured it
out. Thanks very much!

Tom

 

[Leo Heuser]

Hi Leo,

Thanks for your response. I put the following question to Bob also, since
he provided the same solution.

Both the solutions you provided worked, but I am not sure why the second
one works:

=MID(A1,LEN(A1)-1,1)

If the reference cell A1 contains COOP1C, I would think your formula would
return a "C" not "1", which is the correct result.

Here's my thinking (please tell me why I am wrong): The first argument,
A1,
references COOP1C. The second argument, LEN(A1)-1, would return COOP1,
which
is the word from which I want to extract my character. The third argument
is
1, the number of characters I want returned. But doesn't Excel evaluate
the
second argument, COOP1, left to right? So, wouldn't it return a "C"
instead
of a "1"? I understand that it is returning the correct answer, but I
don't
know why.

Thanks!

Tom

Hi Tom

You're welcome.

LEN(A1) returns the *length* of the string in A1, so LEN(A1)-1 is 6-1 or 5.

=MID(A1,LEN(A1)-1,1)

starts at character 5, and picks one character, which amounts to 1.

Leo Heuser

 

[RagDyeR]

In XL, there's always more then 1 way to skin a cat.
Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi RD,

I must say, that second example of yours (the one that returns the reason)
is interesting. I had to stare at it for a few minutes until I figured it
out. Thanks very much!

Tom