Struggle with MID Function

bobgrand

Board Regular
Joined
Apr 14, 2008
Messages
195
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,719
Office Version
2010
Platform
Windows
=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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Another way:

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

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top