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