Replacing characters with numbers in cells

[Juan Valdez]

[[ 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!

 

[Ron Rosenfeld]

[[ 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

 

[Harlan Grove]

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),"{ABCDEFGHI")-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)))

 

[Ron Rosenfeld]

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

Nice.
--ron

 

[Rick Rothstein \(MVP - VB\)]

=SUBSTITUTE(A1,RIGHT(A1,1),FIND(RIGHT(A1,1),"{ABCDEFGHI")-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

 

[Dana DeLouis]

Just another variation:

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

--
Dana DeLouis