# More than one value in MID formule

1. ## More than one value in MID formule

So this is my formula:
+IF(MID(B9;{3;8};4)="A00B";\$B\$68)

What I want to do:
- If start from the third caracter and the 4 caracters after it are equal to "A00B", I want it to count the cell B68
- If start from the eight caracter and the 4 caracters after it are equal to "A00B", I want it to count the cell B68

I know I can make two different formulas but I want to keep it in one since I will have to use a lot.  Register To Reply

2. ## Re: More than one value in MID formule

Formula:  `Please Login or Register  to view this content.`

?
(untested..)  Register To Reply

3. ## Re: More than one value in MID formule

Thanks for your reply, I've tested it and it seems like it wont work.

There should be a way to work with these: { and }, but I don't know how. My complete fomula is now way too long and I want to make it shorter.  Register To Reply

4. ## Re: More than one value in MID formule

you could also try
=IF(OR(MID(B9;{3;8};4)="A00B");\$B\$68;"")  Register To Reply

5. ## Re: More than one value in MID formule

it won't work? in what way won't it work? (I found a missing ")" and edited the formula at some point... try again?)

if it still doesn't work then can you describe the problem or upload a sample so I have something to test against?

as for creating the array in the middle of the mid() formula... I've tried to do something like that once or twice and been totally unsuccessful... what is "way too long"? is can you show us the rest of the formula and maybe we can come up w/ other ways to make it more efficient?  Register To Reply

6. ## Re: More than one value in MID formule or just use martin's solution...  Register To Reply

7. ## Re: More than one value in MID formule

Ok, I'll try to explain:

I have to make the price goes automatic with these codes:
CFA00BVP02BVP, as you can see when you start from the third caracter and the 4 after are equel to "A00B" than it should calculate a price which in mention in another cel. But I have lots of these as you can see below, and I want it to make it shorter because now I use this (for example):
+IF(MID(B9;3;4)="A00B";\$B\$68)+IF(MID(B9;8;4)="A00B";\$B\$68). But I want to make it in 1 code only.

This is the complete formula:
+IF(MID(B9;1;1)="R";\$H\$90)+IF(MID(B9;1;1)="Z";\$H\$76)+IF(MID(B9;3;4)="A00B";\$B\$68)+IF(MID(B9;8;4)="A00B";\$B\$68)+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="E";IF(OR(MID(B9;4;2)={"09";"02";"01"});\$B\$70;\$B\$71)))+IF(MID(B9;8;1)="P";IF(MID(B9;11;1)="E";IF(OR(MID(B9;9;2)={"09";"02";"01"});\$B\$70;\$B\$71)))+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="B";IF(OR(MID(B9;4;2)={"09";"02";"01"});0;\$B\$69)))+IF(MID(B9;8;1)="P";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02";"01"});0;\$B\$69)))+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="H";IF(MID(B9;4;2)="02";\$B\$72;\$B\$73)))+IF(MID(B9;8;1)="P";IF(MID(B9;11;1)="H";IF(MID(B9;9;2)="02";\$B\$72;\$B\$73)))+IF(MID(B9;3;1)="Q";IF(MID(B9;6;1)="B";IF(OR(MID(B9;4;2)={"09";"02"});\$B\$74)))+IF(MID(B9;8;1)="Q";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02"});\$B\$74)))+IF(MID(B9;3;1)="Q";IF(MID(B9;6;1)="E";IF(OR(MID(B9;4;2)={"09";"02"});\$B\$75)))+IF(MID(B9;8;1)="Q";IF(MID(B9;11;1)="E";IF(OR(MID(B9;9;2)={"09";"02"});\$B\$75)))+IF(MID(B9;3;1)="K";IF(MID(B9;6;1)="B";IF(OR(MID(B9;4;2)={"09";"02"});\$B\$76)))+IF(MID(B9;8;1)="K";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02"});\$B\$76)))+IF(MID(B9;3;1)="K";IF(MID(B9;6;1)="H";IF(OR(MID(B9;4;2)={"09";"02"});\$B\$77)))+IF(MID(B9;8;1)="K";IF(MID(B9;11;1)="H";IF(OR(MID(B9;9;2)={"09";"02"});\$B\$77)))+IF(MID(B9;3;1)="N";IF(MID(B9;6;1)="B";IF(MID(B9;4;2)="09";\$B\$78;\$B\$79)))+IF(MID(B9;8;1)="N";IF(MID(B9;11;1)="B";IF(MID(B9;9;2)="09";\$B\$78;\$B\$79)))+IF(MID(B9;3;1)="N";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)="09";\$B\$80;\$B\$81)))+IF(MID(B9;8;1)="N";IF(MID(B9;11;1)="E";IF(MID(B9;9;2)="09";\$B\$80;\$B\$81)))+IF(MID(B9;3;1)="M";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)="09";\$B\$82;\$B\$83)))+IF(MID(B9;8;1)="M";IF(MID(B9;11;1)="E";IF(MID(B9;9;2)="09";\$B\$82;\$B\$83)))+IF(MID(B9;3;1)="D";IF(MID(B9;6;1)="E";\$B\$84))+IF(MID(B9;8;1)="D";IF(MID(B9;11;1)="E";\$B\$84))+IF(MID(B9;3;1)="4";IF(MID(B9;6;1)="B";IF(MID(B9;4;2)="09";\$B\$85)))+IF(MID(B9;8;1)="4";IF(MID(B9;11;1)="B";IF(MID(B9;9;2)="09";\$B\$85)))+IF(MID(B9;3;1)="4";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)="09";\$B\$86)))+IF(MID(B9;8;1)="4";IF(MID(B9;11;1)="E";IF(MID(B9;9;2)="09";\$B\$86)))+IF(MID(B9;3;4)="B005";\$B\$87)+IF(MID(B9;3;4)="B006";\$B\$88)+IF(MID(B9;3;4)="L005";\$B\$89)+IF(MID(B9;3;4)="L006";\$B\$90)+IF(MID(B9;3;4)="I005";\$B\$91)+IF(MID(B9;3;4)="I006";\$B\$92)+IF(MID(B9;3;4)="I003";\$B\$93)+IF(MID(B9;8;4)="B005";\$B\$87)+IF(MID(B9;8;4)="B006";\$B\$88)+IF(MID(B9;8;4)="L005";\$B\$89)+IF(MID(B9;8;4)="L006";\$B\$90)+IF(MID(B9;8;4)="I005";\$B\$91)+IF(MID(B9;8;4)="I006";\$B\$92)+IF(MID(B9;8;4)="I003";\$B\$93)+IF(MID(B9;8;4)="D095";\$B\$94)+IF(MID(B9;3;4)="D095";\$B\$94)+IF(MID(B9;8;4)="Z095";\$B\$95)+IF(MID(B9;3;4)="Z095";\$B\$95)+IF(MID(B9;8;4)="P021";\$B\$96)+IF(MID(B9;3;4)="P021";\$B\$96)+IF(MID(B9;7;1)="A";\$B\$97)  Register To Reply

8. ## Re: More than one value in MID formule

see post #4 you can use {} however i suspect op never changed delimiters from , to ;  Register To Reply

9. ## Re: More than one value in MID formule

Perhaps
=IF(ISNUMBER(FIND("A00B";B9));\$B\$68;"")

Though this doesn't rely on it appearing at the 3rd or 8th position of the string.
It just looks if it exists anywhere in the string.  Register To Reply

10. ## Re: More than one value in MID formule

Too bad, I've tried Martins code and it doesn't work either, I don't know what the problem could be.  Register To Reply

11. ## Re: More than one value in MID formule

The code of Martin worked but not together:
- If I change the 3rd caracter it's good
- If I change the 8th caracter it's good
- But when the 3rd and the 8th are changed it only works for one and it should count both

EDIT: Ok, as you can see:
prnts.jpg

The start price is 39,51€ (G76), and when it's two times "A00B" it should be 38,39€ (A68 = 39,51 - 2x0,56€) but it's only 38,95€ which is 39,51€-0,56€.  Register To Reply

12. ## Re: More than one value in MID formule

=sumproduct(--(mid(b9,{3;8},4)="a00b"))*b68  Register To Reply

13. ## Re: More than one value in MID formule Originally Posted by martindwilson =sumproduct(--(mid(b9,{3;8},4)="a00b"))*b68
Thanks Martin, this work but I have no idea how the formule works, could you please explain so I can advance with my other codes?  Register To Reply

14. ## Re: More than one value in MID formule

=sumproduct(--(mid(b9,{3;8},4)="a00b"))*b68
take
xxA00BXA00Bxxxxxx
=sumproduct(--(mid(xxA00BXA00Bxxxxxx,{3;8},4)="a00b"))*b68
=sumproduct(--("a00b","a00b"}="a00b"))*b68
=sumproduct(--({TRUE;TRUE}))*b68
=sumproduct(--{TRUE;TRUE})*b68

--{TRUE,TRUE} = {1;1}
SUMPRODUCT adds those together to =2
so 2*B68
if it was
say
xxx00BXA00Bxxxxxx
you'd end up with --{FALSE,TRUE} = {0;1}
i.e 1*B68

and
xxx00BXx00Bxxxxxx
you'd end up with --{FALSE,FALSE} = {0;0}
I.E 0*B68  Register To Reply

15. ## Re: More than one value in MID formule

OK, thanks alot for you help, I will try to manage it with this formula.  Register To Reply