|
Making A Code
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 |
Making A Code
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 |
Making A Code
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 |
Making A Code
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 |
Making A Code
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 |
| All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com