extract and assign numerics to characters
 

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

extract and assign numerics to characters
 

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

extract and assign numerics to characters
 

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

extract and assign numerics to characters
 

Ron, Thank you alot especially for the explanation, it works fine and
i understood the process. thank you

extract and assign numerics to characters
 

On Sun, 16 Nov 2008 12:12:59 -0800 (PST), Totti
wrote:

Ron, Thank you alot especially for the explanation, it works fine and
i understood the process. thank you

Glad to help. Thanks for the feedback.
--ron

extract and assign numerics to characters
 

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

extract and assign numerics to characters
 

Very good pickup Rick,
You are right, by chance i didnt have any product starting by AA, but
when i read your thread, intentionaly i changed one to AA and it turn
to "0".
Thank you very much

extract and assign numerics to characters
 

On Sun, 16 Nov 2008 22:40:04 -0500, "Rick Rothstein"
wrote:

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

Nice pickup and solution.
--ron