Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    4 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    4 Star Lounger
    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

  4. #4
    4 Star Lounger
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting on Mixed data (2003)

    What are the *exact* conditions under which you want to highlight a cell?

  6. #6
    4 Star Lounger
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  8. #8
    4 Star Lounger
    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)

  10. #10
    4 Star Lounger
    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  12. #12
    4 Star Lounger
    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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 non-numeric 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 non-numeric value.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •