Conditional Format Problem

[dwgnome]

Hi all,

Looking for help on a conditional format formula that is based on a text range within a cell. In my case, I would like to compare the low and high range when inputted as a number followed by the quotation marks followed by space then hyphen, space then number and ending in quotation marks. The quotation mark is being used to represent inches.

Like this in cell D14: 0.2” - 2.2”

I am using the following condition format formula which works for the strict case above.

=OR(D16<LEFT($D$14,3)+0,D16>MID($D$14,8,3)+0)

However, there will be times when users using this spreadsheet may leave out the spaces on either side of the hyphen, or add more than one space between the number and hyphen. Also if additional digits are added, my formula above does not include all the digits in the results. I prefer to leave the quotation marks in.

I was able to come up with a great solution using Laurent Longre's WMID from (Morefunc add-in). In testing within the worksheet, it worked, but when I attempted to place in the condition format, I got the warning message saying “You may not use references to other worksheets or workbooks for conditional formatting criteria.”

The formula that works is:

=OR(D16<WMID(D$14,1,1,"""")*1,D16>1*SUBSTITUTE(WMID(D$14,2,1,"-"),"""",0,1))

I then split the formula above into two named ranges:

HighS16 =1*SUBSTITUTE(WMID('OCT 09S'!D$14,2,1,"-"),"""",0,1)
LowS16 =WMID('OCT 09S'!D$14,1,1,"""")*1

And substituted back in the conditional format as:

=OR(D16<LowS16,D16>HighS16)

If expression is TRUE I have it set to highlight in red. But for some reason, the conditional format is not working.

Perhaps using named ranges would not be advantageous, since I have multiple columns with differing ranges. I would have to develop a separate named range for each column, making it harder to just drag and copy across and down.

Is there a work around to allow use of this formula or an equivalent non Morefunc formula that I can use?

Any assistance is appreciated.

 

[dwgnome]

Bump.

 

[mikerickson]

Are your numbers always 3 digits long?

if so
=MID(A1,FIND("~",SUBSTITUTE(A1,"""","~",1)&"---~")-3,3)+0 will return the first number

=MID(A1,FIND("~",SUBSTITUTE(A1,"""","~",2)&"---~")-3,3)+0 will return the second

 

[dwgnome]

Not really, they can be of any length. See examples:

Essentially, I need a flexible formula that can accommodate the following examples:

Text Range in one Cell		Expected  extracted number
0.254” - 2.2”			Low = 0.254	High = 2.2
0.422”  - 2.2”			Low = 0.422	High = 2.2
0.58”  -      2.2”		Low = 0.58	High = 2.2
0.067”-2.25”			Low = 0.067	High = 2.25
1.25”  - 2.25”			Low = 1.25	High = 2.25

I will try out your suggestion in the meantime.
Thank you for chiming in.

 

[dwgnome]

mikerickson,

I tried your formula suggestion and it is very flexible as far as the number of spaces between the two numbers and works even without the hyphen. However, it does not accomodate additional digits. Is there a way to tweak the formula to count the number of digits relative to position of the quotation mark and have it grab all digits?

 

[mikerickson]

When I get off work, I'll figure out some monster formula to do that (I hope). But, this issue is an example of the probelms that flow from putting mulitple pieces of data in a single cell.
It would be best to put LowNumber and HighNumber in different columns.

 

[dwgnome]

I quite agree. In fact putting the two numbers in different columns would eliminate the problem. However, the form for which I am using is an agency regulatory mandated recordkeeping form that cannot be reconfigured.

It is not my intent of pushing the free advice/assistance envelope of this forum.

 

[mikerickson]

No, the envelope is not pushed. Its just a general complaint that the "make it look nice" people get put in charge of the "make it work" folks in many organizations.

< old guy exits, mumbling something inaudable about bosses. >

 

[mikerickson]

I did this using names for clarity. If your 2.34" - 34.5" is in A1 then,

Name: firstChr
RefersTo: =FIND("~",SUBSTITUTE(Sheet2!$A1,"""","~",1)&"---~")

Name: firstSubstrings
RefersTo: =MID(Sheet2!$A1,firstChr-ROW(INDIRECT("1:20")),ROW(INDIRECT("1:20")))

Name: firstNumber
RefersTo: =VALUE(VLOOKUP("z",IF(ISNUMBER(VALUE(firstSubstrings)),(firstSubstrings)),1))

Then putting =firstNumber in a cell will return the value of the first number as a number.

secondChr can be done by replacing the underlined 1 in the definiton of firstChr.
secondSubstrings and secondNumber are the same (substituting the proper names)

 

[mikerickson]

Here's a fuller listing of the names. The worksheet formulas =firstNumber and =secondNumber should be put in the appropriate cells.

Name: cleanString
RefersTo: =SUBSTITUTE(SUBSTITUTE(Sheet1!$A1,"-","")," ","")&" "&CHAR(34)&"99E+99"&CHAR(34)


Name: firstChr
RefersTo: =FIND("~",SUBSTITUTE(cleanString,"""","~",1))

Name: firstSubstrings
RefersTo: =MID(cleanString, firstChr-ROW(INDIRECT("1:20")), ROW(INDIRECT("1:20")))

Name: firstNumber
RefersTo: =VALUE(VLOOKUP("z", IF(ISNUMBER(VALUE(firstSubstrings)),(firstSubstrings)), 1))


Name: secondChr
RefersTo: =FIND("~",SUBSTITUTE(cleanString,"""","~",2))

Name: secondSubstrings
RefersTo: =MID(cleanString, secondChr-ROW(INDIRECT("1:20")), ROW(INDIRECT("1:20")))

Name: secondNumber
RefersTo: =VALUE(VLOOKUP("z", IF(ISNUMBER(VALUE(secondSubstrings)),(secondSubstrings)), 1))