Thread: Conditional Formatting with IF Statement

Originally Posted by Paul

Original Formula:

=AND(A3>=LEFT(A2,FIND("-",A2)-1)+0,A3<=SUBSTITUTE(MID(A2,FIND("-",A2)+1,LEN(A2))," ASF","")+0)

The AND() function tests each of the conditions within and returns TRUE or FALSE. All must be TRUE in order for the entire function to be TRUE. In my AND formula above there are two conditions.

First Condition: A3>=LEFT(A2,FIND("-",A2)-1)+0

FIND("-",A2) finds the position of the first hyphen in cell A2. This hyphen will be to the right of your first number. It then subtracts 1 from that position to exclude the hyphen in the LEFT function.

LEFT(A2,FIND("-",A2)-1)+0 uses that position-1 as the number of characters from the left you want to return. It then adds 0 to that "number" so it is converted to a real number and not just a text string representation of a number.

=LEFT("10-15 ASF",FIND("-","10-15 ASF")-1)+0 becomes
=LEFT("10-15 ASF",3-1)+0
=LEFT("10-15 ASF",2)+0
="10"+0
=10

The condition test then compares A3 to that value, and if A3 is greater than or equal to that value, then the condition returns TRUE.

Second condition: A3<=SUBSTITUTE(MID(A2,FIND("-",A2)+1,LEN(A2))," ASF","")+0

The MID function, MID( string , start position , length ), also looks for the first hyphen (notice the same FIND function) but then ADDS one spot to that position so that it starts returning text from one position to the right of that hyphen. The LEN() function at the end tells it to return as many characters exist in that cell. (Even though we're not starting at the first character, if you tell the MID function to return 20 characters in a cell only containing 15, you will only get a max of 15 characters, not 15 characters plus five spaces.)

=MID(A2,FIND("-",A2)+1,LEN(A2)) becomes
=MID("10-15 ASF",FIND("-","10-15 ASF")+1,LEN("10-15 ASF")
=MID("10-15 ASF",3+1,9)
=MID("10-15 ASF",4,9)
=15 ASF

The MID function is then wrapped in a SUBSTITUTE function to eliminate the " ASF" portion of the string and just return the second number (15). SUBSTITUTE( original text , text to be replaced , text to replace with )

SUBSTITUTE("15 ASF"," ASF","") means substitute the " ASF" in "15 ASF" with nothing (an empty string). We then add 0 to the remaining string, "15", to convert it to a numeric value (15).

A3 is once again compared with this second value, and if A3 is less than or equal to the value TRUE is returned. If either condition fails (returns FALSE), the entire function will return FALSE and not apply the conditional formatting.

Hopefully this helps you and others who are looking to do the same or similar tasks.

Thanks! I read through the code and was a bit lost, but you explained that very well. If more people explained the code as you did wow amazing what people could learn!! I will benefit from your post just knowing how to separate data like that. I have had problems in the past and never figured it out!! Thanks for the instruction as well as the answer.