Replacing characters with numbers in cells
 

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

Replacing characters with numbers in cells
 

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

Replacing characters with numbers in cells
 

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

Replacing characters with numbers in cells
 

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

Replacing characters with numbers in cells
 

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

Replacing characters with numbers in cells
 

=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

Replacing characters with numbers in cells
 

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!