Hi to all,
Thanks For the help, I'm getting closer to my objective. I'm sorry but
I don't understand how this formula works, therefore I am not sure how
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 is
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 any
cents, only even dollar amounts up to 9,999.

Thanks so much to the gods of Excel.:)

Nathan Sargeant





Bernard Liengme Wrote:
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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"natei6" wrote
in
message ...

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

Bernard Liengme Wrote:
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!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"natei6"
wrote
in
message ...

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


--
natei6


------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread:
http://www.excelforum.com/showthread...hreadid=512965



--
natei6

------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread:
http://www.excelforum.com/showthread...hreadid=512965



--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=512965