| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Price Code Help Please
=IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi
I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi
Try this: =VALUE(FIND(LEFT(F13,1),F11)&FIND(MID(F13,2,1),F11 )&FIND(MID(F13,3,1),F11)&FIND(MID(F13,4,1),F11)) I've got BLACKHORSE in F11 and LACK in F13. It works for four letters but it may be that you need more/less than four. Make sure it does what you want first and then post back. Andy. "natei6" wrote in message ... Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Andy, I must be brain dead this morning, I meant to say the opposite, enter: "12345" Result: "BLACK" and so on. Thanks again. Hi Try this: =VALUE(FIND(LEFT(F13,1),F11)&FIND(MID(F13,2,1),F11 )&FIND(MID(F13,3,1),F11)&FIND(MID(F13,4,1),F11)) I've got BLACKHORSE in F11 and LACK in F13. It works for four letters but it may be that you need more/less than four. Make sure it does what you want first and then post back. Andy. "natei6" wrote in message ... Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Will it always be 5 numbers? If not, what is the maximum?
Andy. "natei6" wrote in message ... Andy, I must be brain dead this morning, I meant to say the opposite, enter: "12345" Result: "BLACK" and so on. Thanks again. Hi Try this: =VALUE(FIND(LEFT(F13,1),F11)&FIND(MID(F13,2,1),F11 )&FIND(MID(F13,3,1),F11)&FIND(MID(F13,4,1),F11)) I've got BLACKHORSE in F11 and LACK in F13. It works for four letters but it may be that you need more/less than four. Make sure it does what you want first and then post back. Andy. "natei6" wrote in message ... Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Andy, It will always be between 1 and 5 whole numbers. Nate Will it always be 5 numbers? If not, what is the maximum? Andy. "natei6" wrote in message ... Andy, I must be brain dead this morning, I meant to say the opposite, enter: "12345" Result: "BLACK" and so on. Thanks again. Hi Try this: =VALUE(FIND(LEFT(F13,1),F11)&FIND(MID(F13,2,1),F11 )&FIND(MID(F13,3,1),F11)&FIND(MID(F13,4,1),F11)) I've got BLACKHORSE in F11 and LACK in F13. It works for four letters but it may be that you need more/less than four. Make sure it does what you want first and then post back. Andy. "natei6" wrote in message ... Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Try this:
=MID(F40,MID(F42,1,1),1)&IF(LEN(F42)1,MID(F40,MID (F42,2,1),1),"")&IF(LEN(F42)2,MID(F40,MID(F42,3,1 ),1),"")&IF(LEN(F42)3,MID(F40,MID(F42,4,1),1),"") &IF(LEN(F42)4,MID(F40,MID(F42,5,1),1),"") I've got my BLACKHORSE in F40 and my number in F42 Andy. "natei6" wrote in message ... Andy, It will always be between 1 and 5 whole numbers. Nate Will it always be 5 numbers? If not, what is the maximum? Andy. "natei6" wrote in message ... Andy, I must be brain dead this morning, I meant to say the opposite, enter: "12345" Result: "BLACK" and so on. Thanks again. Hi Try this: =VALUE(FIND(LEFT(F13,1),F11)&FIND(MID(F13,2,1),F11 )&FIND(MID(F13,3,1),F11)&FIND(MID(F13,4,1),F11)) I've got BLACKHORSE in F11 and LACK in F13. It works for four letters but it may be that you need more/less than four. Make sure it does what you want first and then post back. Andy. "natei6" wrote in message ... Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate Try this: =MID(F40,MID(F42,1,1),1)&IF(LEN(F42)1,MID(F40,MID (F42,2,1),1),"")&IF(LEN(F42)2,MID(F40,MID(F42,3,1 ),1),"")&IF(LEN(F42)3,MID(F40,MID(F42,4,1),1),"") &IF(LEN(F42)4,MID(F40,MID(F42,5,1),1),"") I've got my BLACKHORSE in F40 and my number in F42 Andy. "natei6" wrote in message ... Andy, It will always be between 1 and 5 whole numbers. Nate Will it always be 5 numbers? If not, what is the maximum? Andy. "natei6" wrote in message ... Andy, I must be brain dead this morning, I meant to say the opposite, enter: "12345" Result: "BLACK" and so on. Thanks again. Hi Try this: =VALUE(FIND(LEFT(F13,1),F11)&FIND(MID(F13,2,1),F11 )&FIND(MID(F13,3,1),F11)&FIND(MID(F13,4,1),F11)) I've got BLACKHORSE in F11 and LACK in F13. It works for four letters but it may be that you need more/less than four. Make sure it does what you want first and then post back. Andy. "natei6" wrote in message ... Hi Andy, Thanks for the response. I'm wanting to type LACK in a cell and to return 2345. Thanks again Nate Hi I don't really understand your aim. Are you wanting to type LACK in a cell and to return 2345? Or are you wanting to type 2345 into a cell and return LACK? Or am I completely on the wrong tack? (pun intended!) Andy. "natei6" wrote in message ... =IF(ISNUMBER(A3),MID($G$1,A3/1000+1,1)&MID($G$1,MOD(A3,1000)/100+1,1)&MID($G$1,MOD(A3,100)/10+1,1)&MID($G$1,MOD(A3,10)+1,1)&MID($G$1,(A3-INT(A3))*10+1,1)&MID($G$1,ROUND((A3*10-INT(A3*10)),0)*10+1,1),"") Hi to all, My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers. Help with this code would be so 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=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
=IF(A20,"Q"&MID($G$1,MID(A2,1,1),1)&IF(LEN(A2)1, MID($G$1,MID(A2,2,1),1),"")&IF(LEN(A2)2,MID($G$1, MID(A2,3,1),1),"")&IF(LEN(A2)3,MID($G$1,MID(A2,4, 1),1),"")&IF(LEN(A2)4,MID($G$1,MID(A2,5,1),1),"") ,"Q") Hi, Andy gave me this formula and it is for making a price code. I have BLACKHORSE in G1 and the formula in B2. It works beautifully as long as the number in A2 does not contain a zero, in which case an error (Value#!) results. Any help in modifying this awsome formula to correct this would be greatly appreciated. Nate natei6 Wrote: Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hmmm. This could get complicated! What is the maximum number you would put
in? Is it always based on the word BLACKHORSE? Andy. "natei6" wrote in message ... =IF(A20,"Q"&MID($G$1,MID(A2,1,1),1)&IF(LEN(A2)1, MID($G$1,MID(A2,2,1),1),"")&IF(LEN(A2)2,MID($G$1, MID(A2,3,1),1),"")&IF(LEN(A2)3,MID($G$1,MID(A2,4, 1),1),"")&IF(LEN(A2)4,MID($G$1,MID(A2,5,1),1),"") ,"Q") Hi, Andy gave me this formula and it is for making a price code. I have BLACKHORSE in G1 and the formula in B2. It works beautifully as long as the number in A2 does not contain a zero, in which case an error (Value#!) results. Any help in modifying this awsome formula to correct this would be greatly appreciated. Nate natei6 Wrote: Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi
Provided that you are not committed to having 1 being represented by B, then you should consider B to be 0, L to be 1 etc. and just add +1 to the Mid function. =IF(A20,"Q"&MID($G$1,MID(A2,1,1)+1,1) &IF(LEN(A2)1,MID($G$1,MID(A2,2,1)+1,1),"") &IF(LEN(A2)2,MID($G$1,MID(A2,3,1)+1,1),"") &IF(LEN(A2)3,MID($G$1,MID(A2,4,1)+1,1),"") &IF(LEN(A2)4,MID($G$1,MID(A2,5,1)+1,1),""),"Q" ) Obviously if you are going to go above the 5 digits that you gave Andy as your maximum earlier in the thread, then you would just need to add further lines stepped up in sequence as above. -- Regards Roger Govier "natei6" wrote in message ... =IF(A20,"Q"&MID($G$1,MID(A2,1,1),1)&IF(LEN(A2)1, MID($G$1,MID(A2,2,1),1),"")&IF(LEN(A2)2,MID($G$1, MID(A2,3,1),1),"")&IF(LEN(A2)3,MID($G$1,MID(A2,4, 1),1),"")&IF(LEN(A2)4,MID($G$1,MID(A2,5,1),1),"") ,"Q") Hi, Andy gave me this formula and it is for making a price code. I have BLACKHORSE in G1 and the formula in B2. It works beautifully as long as the number in A2 does not contain a zero, in which case an error (Value#!) results. Any help in modifying this awsome formula to correct this would be greatly appreciated. Nate natei6 Wrote: Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Thanks for the help Roger, My coworkers have been using this code for many years and making B represent 0 rather than E might cause revolt, is there any way to make the E represent 0? It would get me out of a lot of trouble. Thanks again Nate Roger Govier Wrote: Hi Provided that you are not committed to having 1 being represented by B, then you should consider B to be 0, L to be 1 etc. and just add +1 to the Mid function. =IF(A20,"Q"&MID($G$1,MID(A2,1,1)+1,1) &IF(LEN(A2)1,MID($G$1,MID(A2,2,1)+1,1),"") &IF(LEN(A2)2,MID($G$1,MID(A2,3,1)+1,1),"") &IF(LEN(A2)3,MID($G$1,MID(A2,4,1)+1,1),"") &IF(LEN(A2)4,MID($G$1,MID(A2,5,1)+1,1),""),"Q" ) Obviously if you are going to go above the 5 digits that you gave Andy as your maximum earlier in the thread, then you would just need to add further lines stepped up in sequence as above. -- Regards Roger Govier "natei6" wrote in message ... =IF(A20,"Q"&MID($G$1,MID(A2,1,1),1)&IF(LEN(A2)1, MID($G$1,MID(A2,2,1),1),"")&IF(LEN(A2)2,MID($G$1, MID(A2,3,1),1),"")&IF(LEN(A2)3,MID($G$1,MID(A2,4, 1),1),"")&IF(LEN(A2)4,MID($G$1,MID(A2,5,1),1),"") ,"Q") Hi, Andy gave me this formula and it is for making a price code. I have BLACKHORSE in G1 and the formula in B2. It works beautifully as long as the number in A2 does not contain a zero, in which case an error (Value#!) results. Any help in modifying this awsome formula to correct this would be greatly appreciated. Nate natei6 Wrote: Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
The highest number would be 99,999, and the code is always based on BLACKHORSE natei6 Wrote: Thanks for the help Roger, My coworkers have been using this code for many years and making B represent 0 rather than E might cause revolt, is there any way to make the E represent 0? It would get me out of a lot of trouble. Thanks again Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
I presume the input cell is formatted as text - otherwise 0159 would just
appear as 159. Andy. "natei6" wrote in message ... The highest number would be 99,999, and the code is always based on BLACKHORSE natei6 Wrote: Thanks for the help Roger, My coworkers have been using this code for many years and making B represent 0 rather than E might cause revolt, is there any way to make the E represent 0? It would get me out of a lot of trouble. Thanks again Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi Nate
I can understand the revolt if they have been using it for years. You could always "cheat" however. Have the visible cell say BLACKHORSE, but in a hidden cell (White font on White background) in say G2, enter EBLACKHORS. Change my formula to reference $G$2 instead. What they don't know ....... etc. -- Regards Roger Govier "natei6" wrote in message ... Thanks for the help Roger, My coworkers have been using this code for many years and making B represent 0 rather than E might cause revolt, is there any way to make the E represent 0? It would get me out of a lot of trouble. Thanks again Nate Roger Govier Wrote: Hi Provided that you are not committed to having 1 being represented by B, then you should consider B to be 0, L to be 1 etc. and just add +1 to the Mid function. =IF(A20,"Q"&MID($G$1,MID(A2,1,1)+1,1) &IF(LEN(A2)1,MID($G$1,MID(A2,2,1)+1,1),"") &IF(LEN(A2)2,MID($G$1,MID(A2,3,1)+1,1),"") &IF(LEN(A2)3,MID($G$1,MID(A2,4,1)+1,1),"") &IF(LEN(A2)4,MID($G$1,MID(A2,5,1)+1,1),""),"Q" ) Obviously if you are going to go above the 5 digits that you gave Andy as your maximum earlier in the thread, then you would just need to add further lines stepped up in sequence as above. -- Regards Roger Govier "natei6" wrote in message ... =IF(A20,"Q"&MID($G$1,MID(A2,1,1),1)&IF(LEN(A2)1, MID($G$1,MID(A2,2,1),1),"")&IF(LEN(A2)2,MID($G$1, MID(A2,3,1),1),"")&IF(LEN(A2)3,MID($G$1,MID(A2,4, 1),1),"")&IF(LEN(A2)4,MID($G$1,MID(A2,5,1),1),"") ,"Q") Hi, Andy gave me this formula and it is for making a price code. I have BLACKHORSE in G1 and the formula in B2. It works beautifully as long as the number in A2 does not contain a zero, in which case an error (Value#!) results. Any help in modifying this awsome formula to correct this would be greatly appreciated. Nate natei6 Wrote: Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi, I wouldn't need 0 at the beginning of a number, only with in the numbers ie 10, 20, 105 etc. Nate I presume the input cell is formatted as text - otherwise 0159 would just appear as 159. Andy. "natei6" wrote in message ... The highest number would be 99,999, and the code is always based on BLACKHORSE natei6 Wrote: Thanks for the help Roger, My coworkers have been using this code for many years and making B represent 0 rather than E might cause revolt, is there any way to make the E represent 0? It would get me out of a lot of trouble. Thanks again Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Thanks much, I will give that a try. Nate Roger Govier Wrote: Hi Nate I can understand the revolt if they have been using it for years. You could always "cheat" however. Have the visible cell say BLACKHORSE, but in a hidden cell (White font on White background) in say G2, enter EBLACKHORS. Change my formula to reference $G$2 instead. What they don't know ....... etc. -- Regards Roger Govier "natei6" wrote in message ... Thanks for the help Roger, My coworkers have been using this code for many years and making B represent 0 rather than E might cause revolt, is there any way to make the E represent 0? It would get me out of a lot of trouble. Thanks again Nate Roger Govier Wrote: Hi Provided that you are not committed to having 1 being represented by B, then you should consider B to be 0, L to be 1 etc. and just add +1 to the Mid function. =IF(A20,"Q"&MID($G$1,MID(A2,1,1)+1,1) &IF(LEN(A2)1,MID($G$1,MID(A2,2,1)+1,1),"") &IF(LEN(A2)2,MID($G$1,MID(A2,3,1)+1,1),"") &IF(LEN(A2)3,MID($G$1,MID(A2,4,1)+1,1),"") &IF(LEN(A2)4,MID($G$1,MID(A2,5,1)+1,1),""),"Q" ) Obviously if you are going to go above the 5 digits that you gave Andy as your maximum earlier in the thread, then you would just need to add further lines stepped up in sequence as above. -- Regards Roger Govier "natei6" wrote in message ... =IF(A20,"Q"&MID($G$1,MID(A2,1,1),1)&IF(LEN(A2)1, MID($G$1,MID(A2,2,1),1),"")&IF(LEN(A2)2,MID($G$1, MID(A2,3,1),1),"")&IF(LEN(A2)3,MID($G$1,MID(A2,4, 1),1),"")&IF(LEN(A2)4,MID($G$1,MID(A2,5,1),1),"") ,"Q") Hi, Andy gave me this formula and it is for making a price code. I have BLACKHORSE in G1 and the formula in B2. It works beautifully as long as the number in A2 does not contain a zero, in which case an error (Value#!) results. Any help in modifying this awsome formula to correct this would be greatly appreciated. Nate natei6 Wrote: Many Many Thanks Andy, That is awsome! I only see one problem, if I enter the number 10 I get an error (#Value), is there a solution to that? Thanks many times again, Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi, I just want to extend a very sincere heartfelt thanks to Andy and Roger for all the great help and education to boot. You guys are heroes and gods. Much Appreciation! Nate natei6 Wrote: Thanks much, I will give that a try. Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Price Code Help Please
Hi Nate
Thanks for the feedback. Andy did all the hard work setting up the formula in the first instance. I merely tinkered around the edges dealing with the 0 problem. -- Regards Roger Govier "natei6" wrote in message ... Hi, I just want to extend a very sincere heartfelt thanks to Andy and Roger for all the great help and education to boot. You guys are heroes and gods. Much Appreciation! Nate natei6 Wrote: Thanks much, I will give that a try. Nate -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185 View this thread: http://www.excelforum.com/showthread...hreadid=522990 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| code not unique find latest date | Excel Discussion (Misc queries) | |||
| can you entre a bar code into a excel cell? | Excel Worksheet Functions | |||
| VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
| Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
| Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Linear Mode
