+ Reply to Thread
Results 1 to 22 of 22

Price Code Help Please

  1. #1
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51

    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

  2. #2
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > =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. #3
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > =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
    >

  4. #4
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> >

    >> =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. #5
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> >

    >> =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
    >

  6. #6
    Guest

    Re: Price Code Help Please

    Will it always be 5 numbers? If not, what is the maximum?

    Andy.

    "natei6" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> > 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" <[email protected]>

    >> wrote
    >> >> in
    >> >> message news:[email protected]...
    >> >> >
    >> >> >
    >> >>

    >> =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. #7
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> > 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" <[email protected]>

    >> wrote
    >> >> in
    >> >> message news:[email protected]...
    >> >> >
    >> >> >
    >> >>

    >> =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
    >

  8. #8
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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" <[email protected]> wrote in
    >> message news:[email protected]...
    >> >
    >> > 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" <[email protected]> wrote
    >> >> in
    >> >> message news:[email protected]...
    >> >> >
    >> >> > 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" <[email protected]>
    >> >> wrote
    >> >> >> in
    >> >> >> message

    >> news:[email protected]...
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >>

    >> =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. #9
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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" <[email protected]> wrote in
    >> message news:[email protected]...
    >> >
    >> > 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" <[email protected]> wrote
    >> >> in
    >> >> message news:[email protected]...
    >> >> >
    >> >> > 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" <[email protected]>
    >> >> wrote
    >> >> >> in
    >> >> >> message

    >> news:[email protected]...
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >>

    >> =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
    >

  10. #10
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51

    A Little Modification Help Please

    =IF(A2>0,"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

    Quote Originally Posted by natei6
    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

  11. #11
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > =IF(A2>0,"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. #12
    Roger Govier
    Guest

    Re: 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(A2>0,"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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > =IF(A2>0,"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. #13
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    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

    Quote Originally Posted by Roger Govier
    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(A2>0,"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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > =IF(A2>0,"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
    >

  14. #14
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    The highest number would be 99,999, and the code is always based on BLACKHORSE


    Quote Originally Posted by natei6
    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

  15. #15
    Guest

    Re: Price Code Help Please

    I presume the input cell is formatted as text - otherwise 0159 would just
    appear as 159.

    Andy.

    "natei6" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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. #16
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >

  17. #17
    Roger Govier
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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(A2>0,"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" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> >

    >> =IF(A2>0,"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
    >




  18. #18
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51
    Thanks much, I will give that a try.
    Nate


    Quote Originally Posted by Roger Govier
    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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(A2>0,"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" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> >

    >> =IF(A2>0,"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
    >

  19. #19
    Registered User
    Join Date
    03-15-2004
    Location
    OR, USA
    Posts
    51

    Big Big Thank You!

    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

    Quote Originally Posted by natei6
    Thanks much, I will give that a try.
    Nate

  20. #20
    Roger Govier
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  21. #21
    Registered User
    Join Date
    02-05-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Price Code Help Please

    I needed to do nearly the same thing, and this worked perfectly. One difference,though, which I cannot figure out. We use "BLACKHORSEZ", where "Z" represents a repeated digit.

    For example,
    5595 = KZSK
    1999 = BSZZ

    I would prefer to embed the additional code in Andy's/Roger's solution, but an additional cell performing this step (seeking repeated letters from cost code instead of repeated digits from cost) should still get the job done.

    Hope this makes sense.

  22. #22
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Price Code Help Please

    This thread is more than 6 yrs old. Its better if you open a new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1