Results 1 to 5 of 5

Thread: Struggle with MID Function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Struggle with MID Function

    Hello all,

    In column Y I have quote numbers. In column A I need to put the quote number from column Y if it begins with CP or DC and blank if not.

    I tried this but the result is not right and returning (#VALUE!)

    =IF(MID(Y12,1,2="DC"),Y12,IF(MID(Y12,1,2="CP"),Y12,""))

    Not sure what I am doing wrong here

    Any help would be greatly appreciated.

    Bob

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,248
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Struggle with MID Function

    Quote Originally Posted by bobgrand View Post
    Hello all,

    In column Y I have quote numbers. In column A I need to put the quote number from column Y if it begins with CP or DC and blank if not.

    I tried this but the result is not right and returning (#VALUE!)

    =IF(MID(Y12,1,2="DC"),Y12,IF(MID(Y12,1,2="CP"),Y12,""))

    Not sure what I am doing wrong here
    Your ending parentheses for the MID function are in the wrong location. right now you are testing if 2="DC" and if 2="CP". Try it this way..

    =IF(MID(Y12,1,2)="DC",Y12,IF(MID(Y12,1,2)="CP",Y12,""))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Struggle with MID Function

    =IF(MID(Y12, 1, 2) = "DC", Y12, IF(MID(Y12, 1, 2) = "CP", Y12, ""))

    or

    =IF(OR(MID(Y12, 1, 2) = {"DC","CP"}), Y12, "")

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Struggle with MID Function

    Hi,

    Another way:

    =IF(OR(LEFT(Y12,2)={"DC","CP"}),Y12,"")

  5. #5
    Board Regular
    Join Date
    Apr 2008
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggle with MID Function

    Darn Parentheses!! Thank you for all your help.

    Have a great weekend

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •