Results 1 to 15 of 25

20080115, 16:52 #1
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Conditional Formatting on Mixed data (2003)
Greetings!
I am performing some conditional formatting on a column that has mixed data. Meaning some of the cells are numbers and some are alpha. right now I am using a condition where: Cell Value is greater than or equal to 1000000.
this is catching what I want to highlight, but it is also highlighting the text fields as well.
Is there a way around this?
Thanks,
Brad

20080115, 16:59 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional Formatting on Mixed data (2003)
You can use Formula Is in the first dropdown, and a formula similar to
<code>
=AND(A1>=1000000,ISNUMBER(A1))
</code>
in the box next to it. Replace A1 with the appropriate cell.

20080115, 17:01 #3
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Formatting on Mixed data (2003)
That did it
Thanks!
Brad

20080115, 17:08 #4
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Formatting on Mixed data (2003)
One more thing,
How would this be handled of the cell I want highlighted contains both numbers and text?
ex: 10010010AB
Thks,
Brad

20080115, 17:22 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional Formatting on Mixed data (2003)
What are the *exact* conditions under which you want to highlight a cell?

20080115, 17:33 #6
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Formatting on Mixed data (2003)
where the cell contains all numbers, or the possibility of mixed 10011010AB.
Brad

20080115, 17:36 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional Formatting on Mixed data (2003)
Please try to provide precise and complete information. If A1 contains 37, it contains "all numbers", but do you want to highlight it?

20080115, 17:46 #8
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Formatting on Mixed data (2003)
If the Cell contains either all Numbers, or a MIX of numbers and alpha, highlight the cell.
ex: 1234 or 1234AB
If the cell is all ALPHA do not highlight
ex: alltext
if the cell is BLANK do not highlight
ex:
Brad

20080115, 18:04 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional Formatting on Mixed data (2003)
It would have been clearer if you had indicated that you were asking an entirely new question, I thought it was a followup from the first post in this thread.
There is probably a better way, but you can use this formula:
<code>
=SUM(1*ISERROR(1*MID(A1,ROW($1:$25),1)))<25
</code>
where A1 is the active cell in the range you want to apply conditional formatting to, and 25 is an arbitrary number larger than or equal to the length of the longest text you expect in the cell(s). For example, if you need to accomodate text strings of up to 50 characters, use
<code>
=SUM(1*ISERROR(1*MID(A1,ROW($1:$50),1)))<50
</code>
(The higher the number you use, the greater the calculation overhead, so don't just increase the 25 to 255 or so just because you can)

20080115, 18:13 #10
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Formatting on Mixed data (2003)
Thanks.
I thought it was a continuation....
Brad

20080115, 18:20 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional Formatting on Mixed data (2003)
It's about conditional formatting, yes, but with a completely different condition. The >=1000000 from the first post doesn't play a role any more. Or you misled us by introducing the >=1000000 there.

20080115, 18:39 #12
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Formatting on Mixed data (2003)
the greater that condition was not intended to mislead the issue.
My first attempt to highlight the intended data by using the greater than condition did not do what I expected. upon reviewing the data closer, I found that SOME of the data contained alphanumeric. I also needed to highlight the alphanumeric as well as the all numeric. I did not need to highlight the all alpha data.
Now, with the condition set as you suggest, it is highlighting the cells where the numbers are less than 1000000. this is also not what I need highlighted.
Brad

20080115, 18:41 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional Formatting on Mixed data (2003)
So, I come back to my question from higher up in this thread. Would you please take a moment to describe *exactly* which items you want to be highlighted?

20080115, 19:20 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Conditional Formatting on Mixed data (2003)
Are you looking for something like:
=OR(AND(A1>=1000000,ISNUMBER(A1)),VALUE(LEFT(A1,LE N(A1)2))>1000000)
It presumes that the alphanumerics will be 2 letters at the end
Steve

20080115, 19:36 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Conditional Formatting on Mixed data (2003)
Here is one where the end text can be any length [adjust the 25 to get the full text string or maximum position of the first nonnumeric value.]
=OR(AND(A1>=1000000,ISNUMBER(A1)),VALUE(LEFT(A1,MA TCH(TRUE,ISERROR(VALUE(MID(A1,ROW($1:$<font color=red>25</font color=red>),1))),0)1))>1000000)
It extracts just the number that is up to the first nonnumeric value.
Steve