Excel 2007 : adding a text qualifier to an "if" statement

[jjlad]

Hello,
I have this formula in all the cells in column W:
=IF(ISERROR(IF(V4="Disco",0,IF(V4="A*",1,IF(AG4="D",0,IF(AP4="*disco*",0,IF(V4-$N$1>2,1)))))),0,IF(V4="Disco",0,IF(V4="A*",1,IF(AG4="D",0,IF(AP4="*disco*",0,IF(V4-$N$1>2,1,))))))

The intent of it is to put a 1 or a 0 in that cell so that a pivot table reading it can be filtered to calculate based on only those with a 1.

Sometimes the value in the cell in colum V is text and if so it always starts with "A" but could have anything after the "A" so I tried to say if it starts with "A" followed by anything (thus my "A*"), give me a 1.

Similarly, in column AP, the cell is text and may or may not contain the word "Disco" but if it does I'd like W to be a 0.

The sheet is 12,000 plus rows and works fine except the value it generates for column W doesn't seem to be influenced at all by the text values I've tried to include for columns V or AP, so I currently have to filter for those an manually input the 1 in each cell.

Any advice on this would be much appreciated. (not as much as sound advice on how to make money in today's markets , but definately appreciated! Thanks!!

[se1429]

Hi,

To check for an "A" as a first character in the cell you can use the LEFT function with the IF function the syntax will be something like this:

Please Login or Register  to view this content.

As for the second part you can use the FIND function within your formula, and it is syntax is as follows:

Please Login or Register  to view this content.

The FIND returns the position of the first string within the second string. So If the FIND returns a value greater than 0 then the string is found otherwise it is not.

You may find this post of interest to you:

http://www.exceldigest.com/myblog/20...ons-in-a-cell/

Regards.