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