Price Code Help Please

[natei6]

=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. Thi
formula is giving me BBBLBB for 1. I don't need cents, just whol
numbers. Help with this code would be so appreciated.

Nathan Sargean

 

[Guest]

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]

Hi Andy,
Thanks for the response. I'm wanting to type LACK in a cell
and to return 2345.
Thanks again
Nate

 

[Guest]

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]

Andy,
I must be brain dead this morning, I meant to say the opposite, enter
"12345" Result: "BLACK" and so on.

Thanks again.

 

[Guest]

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

Andy.

 

[natei6]

Andy
It will always be between 1 and 5 whole numbers
Nat

 

[Guest]

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]

Many Many Thanks Andy,
That is awsome! I only see one problem, if I enter the number 10 I ge
an error (#Value), is there a solution to that?
Thanks many times again,
Nate

 

[natei6]

=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 hav
BLACKHORSE in G1 and the formula in B2. It works beautifully as long a
the number in A2 does not contain a zero, in which case an erro
(Value#!) results. Any help in modifying this awsome formula t
correct this would be greatly appreciated.
Nate

 

[Guest]

Hmmm. This could get complicated! What is the maximum number you would put
in? Is it always based on the word BLACKHORSE?

Andy.

 

[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.

 

[natei6]

Thanks for the help Roger,

My coworkers have been using this code for many years and making
represent 0 rather than E might cause revolt, is there any way to mak
the E represent 0? It would get me out of a lot of trouble.

Thanks again Nate

 

[natei6]

The highest number would be 99,999, and the code is always based o
BLACKHORSE

 

[Guest]

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

Andy.

 

[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.

 

[natei6]

Hi,
I wouldn't need 0 at the beginning of a number, only with in th
numbers ie 10, 20, 105 etc.
Nate

 

[natei6]

Thanks much, I will give that a try.
Nate

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

 

[natei6]

Hi,
I just want to extend a very sincere heartfelt thanks to Andy and Roge
for all the great help and education to boot. You guys are heroes an
gods.

Much Appreciation!
Nate

 

[Roger Govier]

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.