View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default 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)))