I think you will need to expand this formula to handle product codes that
begin with one or more A's (they turn into leading zeroes which are dropped
because a number is being returned). Consider this instead...

=TEXT(SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN (A2))),1))-65,10^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))))),REPT("0",LEN(A2)))

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sun, 16 Nov 2008 08:35:39 -0500, Ron Rosenfeld

wrote:

On Sun, 16 Nov 2008 05:14:51 -0800 (PST), Totti
wrote:

Hi everyone,
I have a sheet that contains some products code but with no special
pattern,like the following:

DIJ
HEBGI
IGICD
BH
EFHJI
DHDFI

i want to assign these values, numbers from 0-9 as they are from from
A - J in a different column, i searched the newsgroups for help, i
understood that it would be something involving ROW(INDIRECT("1:"&LEN
(A2)) to be able to do what i am suppose to do in one cell so that DIJ
for instance takes the value of 389 so on so forth.
i tried with my limited knowledge to apply it, i was able to get the
first character only like D - 3 could you please help me on this task?
that i know what i am doing wrong or more generally how should this
task be approached, thanks a lot in advance

=SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2 ))),1))-65,
10^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))))

--ron

To explain this (and you could use the Formula Evaluation tool to follow
along,
also):

We first set up an array of the characters in the initial string:

MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)

We then obtain the ASCII code for each of the letters

CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))

to make the code "zero-based" we subtract the code for the letter A:

CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))-65

This result in an array of 0-9 corresponding to the letters A-J, and in
order.

Next we set up an array of the same size, consisting of descending powers
of
10. So for a three letter word, we need to construct an array which looks
like: 10^2, 10^1, 10^0 or {100,10,1}

Multiplying one array by the other will result in the required result.
--ron