+ Reply to Thread
Results 1 to 15 of 15

More than one value in MID formule

  1. #1
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    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.

    Thanks in advance,

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: More than one value in MID formule

    how about:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ?
    (untested..)

  3. #3
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    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. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: More than one value in MID formule

    you could also try
    =IF(OR(MID(B9;{3;8};4)="A00B");$B$68;"")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    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. #6
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: More than one value in MID formule

    or just use martin's solution...

  7. #7
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    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. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: More than one value in MID formule

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

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #10
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    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. #11
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    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€.
    Last edited by Teddymanne; 08-08-2014 at 11:54 AM.

  12. #12
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: More than one value in MID formule

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

  13. #13
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    Re: More than one value in MID formule

    Quote Originally Posted by martindwilson View Post
    =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. #14
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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. #15
    Registered User
    Join Date
    07-10-2014
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    15

    Re: More than one value in MID formule

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. simplification de formule
    By Nacho15 in forum Non English Excel
    Replies: 14
    Last Post: 11-15-2013, 11:14 AM
  2. formule for tiling
    By redbullrace in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2013, 04:53 PM
  3. dynamic formule
    By glda19 in forum Excel General
    Replies: 2
    Last Post: 12-25-2012, 01:06 PM
  4. [SOLVED] Date formule
    By kr1st0f1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2012, 07:49 AM
  5. Need a formule for this....
    By Jerin John in forum Excel General
    Replies: 1
    Last Post: 03-23-2011, 12:38 AM

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