Struggle with MID Function

[bobgrand]

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

 

[Rick Rothstein]

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,""))

 

[shg]

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

or

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

 

[jtakw]

Hi,

Another way:

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

 

[bobgrand]

Darn Parentheses!! Thank you for all your help.

Have a great weekend