[[ This message was both posted and mailed: see
the "To," "Cc," and "Newsgroups" headers for details. ]]

I have the following data:
00000000000976042B
00000000000637707{
00000000000016326{
00000000000047546F
00000000001484392E
00000000000687370E
00000000000958500{
00000000000771160{
00000000004424795A

The last character of each row requires translation as follows:
{=0
A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9

I'm able to translate the 18th character by using the replace function
but would like a less manual process. I'm a bit outside the box at the
moment and not sure to go from here. Could sure use some help! Thank
you!

=LEFT(A2,17)&IF(RIGHT(A2)="{",0,IF(AND(CODE(RIGHT( A2))=65,CODE(RIGHT(A2))<=73),CODE(RIGHT(A2))-64,"Error"))
--
David Biddulph

"Juan Valdez" wrote in message
...
[[ This message was both posted and mailed: see
the "To," "Cc," and "Newsgroups" headers for details. ]]

I have the following data:
00000000000976042B
00000000000637707{
00000000000016326{
00000000000047546F
00000000001484392E
00000000000687370E
00000000000958500{
00000000000771160{
00000000004424795A

The last character of each row requires translation as follows:
{=0
A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9

I'm able to translate the 18th character by using the replace function
but would like a less manual process. I'm a bit outside the box at the
moment and not sure to go from here. Could sure use some help! Thank
you!

On Tue, 15 Jul 2008 18:55:27 -0400, Juan Valdez wrote:

[[ This message was both posted and mailed: see
the "To," "Cc," and "Newsgroups" headers for details. ]]

I have the following data:
00000000000976042B
00000000000637707{
00000000000016326{
00000000000047546F
00000000001484392E
00000000000687370E
00000000000958500{
00000000000771160{
00000000004424795A

The last character of each row requires translation as follows:
{=0
A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9

I'm able to translate the 18th character by using the replace function
but would like a less manual process. I'm a bit outside the box at the
moment and not sure to go from here. Could sure use some help! Thank
you!


=LEFT(A1,LEN(A1)-1) & VLOOKUP(RIGHT(A1,1),
{"{",0;"A",1;"B",2;"C",3;"D",4;"E",5;"F",6;"G",7;" H",8;"I",9},2,FALSE)
--ron

Juan Valdez wrote...
I have the following data:
00000000000976042B
00000000000637707{
00000000000016326{
00000000000047546F
00000000001484392E
00000000000687370E
00000000000958500{
00000000000771160{
00000000004424795A

The last character of each row requires translation as follows:
{=0
A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9
....

Another option,

=SUBSTITUTE(A1,RIGHT(A1,1),FIND(RIGHT(A1,1),"{ABCD EFGHI")-1)

or, with error reporting,

=SUBSTITUTE(A1,RIGHT(A1,1),IF(COUNT(FIND(RIGHT(A1, 1),"{ABCDEFGHI")),
FIND(RIGHT(A1,1),"{ABCDEFGHI")-1," -error: "&RIGHT(A1,1)))

On Tue, 15 Jul 2008 18:30:13 -0700 (PDT), Harlan Grove
wrote:

=SUBSTITUTE(A1,RIGHT(A1,1),FIND(RIGHT(A1,1),"{ABC DEFGHI")-1)

Nice.
--ron

=SUBSTITUTE(A1,RIGHT(A1,1),FIND(RIGHT(A1,1),"{ABCD EFGHI")-1)

This is, of course, a general solution (and a nice one at that); however, if
the OP's data items are always 18 characters long (as his posted example
seems to suggest), you can save a function call by modifying your formula
like this...

=LEFT(A1,17)&(FIND(RIGHT(A1),"{ABCDEFGHI")-1)

Rick

Just another variation:

=LEFT(A1,17)&MOD(1302838,CODE(RIGHT(A1))-62)

--
Dana DeLouis


"Juan Valdez" wrote in message ...

[[ This message was both posted and mailed: see
the "To," "Cc," and "Newsgroups" headers for details. ]]

I have the following data:
00000000000976042B
00000000000637707{
00000000000016326{
00000000000047546F
00000000001484392E
00000000000687370E
00000000000958500{
00000000000771160{
00000000004424795A

The last character of each row requires translation as follows:
{=0
A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9

I'm able to translate the 18th character by using the replace function
but would like a less manual process. I'm a bit outside the box at the
moment and not sure to go from here. Could sure use some help! Thank
you!