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

Formula:

?
(untested..)

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.

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

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

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?

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

or just use martin's solution...

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)

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

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

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.

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.

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

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

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

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?

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

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

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

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

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