| 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 |
| 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 | |||
Hybrid Mode
