Making A Code

N

natei6

Is there a way to convert a column of prices into code. For example:
BLACK HORSE for 1234567890? Does this require a formula, Vlookup,
Formatting? Any help appreciated. Nathan Sargeant
 
B

Bernard Liengme

This seems to work with real numbers ('prices') up to 9999.99
=MID($G$1,A1/1000+1,1)&MID($G$1,MOD(A1,1000)/100+1,1)&MID($G$1,MOD(A1,100)/10+1,1)&MID($G$1,MOD(A1,10)+1,1)&MID($G$1,(A1-INT(A1))*10+1,1)&MID($G$1,ROUND((A1*10-INT(A1*10)),0)*10+1,1)
Should be tested and you will need a decoder!
 
N

natei6

Thanks Bernard,
This is seems to be close but it also has a few issues. When I enter
123.45, the result is BLACK. When I enter 1234.56 the result is LACKH.
If I enter "1" the result is BBBLBB. It appears 0 - B when 1 should
equal "B". Also, a blank cell resuts in "BBBBBB". Is there a way to
resolve these issues, and if not, is it possible to program this in sql
code?
Again, many thanks,
Nathan Sargeant
 
B

Bernard Liengme

In my code 0 becomes B (in G1 I had BLACKADDER but BLACKHORSE works)
We need a zero to code numbers like 105
For the blank problem use =IF(ISNUMBER(A1), mid(.........), "")
best wishes
 
N

natei6

Hi to all,
Thanks For the help, I'm getting closer to my objective. I'm sorry bu
I don't understand how this formula works, therefore I am not sure ho
to modify it.

=IF(ISNUMBER(A1),MID($G$1,A1/1000+1,1)&MID($G$1,MOD(A1,1000)/100+1,1)&MID($G$1,MOD(A1,100)/10+1,1)&MID($G$1,MOD(A1,10)+1,1)&MID($G$1,(A1-INT(A1))*10+1,1)&MID($G$1,ROUND((A1*10-INT(A1*10)),0)*10+1,1),"")

I would like to modify this formula to achieve the following results.

Currently, if the code is BLACKHORSE, the result of 0 in cell a1 i
BBBBBB. I would like the result to be simply "B" for 1 and "E" for 0.
In other words 1="B", 2="L", 120="BLE" and so forth. I don't need an
cents, only even dollar amounts up to 9,999.

Thanks so much to the gods of Excel.:)

Nathan Sargeant
 
Top