extract and assign numerics to characters

  • Thread starter Thread starter Totti
  • Start date Start date
T

Totti

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
 
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
Click to expand...

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

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

--ron
Click to expand...

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
 
Ron, Thank you alot especially for the explanation, it works fine and
i understood the process. thank you
 
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)))
 
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
 
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...
Click to expand...

Nice pickup and solution.
--ron
 
Back
Top