+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting based on numeric value stored as text

  1. #1
    Registered User
    Join Date
    03-07-2009
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Conditional formatting based on numeric value stored as text

    I have a large workbook with many cells that contain zeros formatted as text.
    I'd like to make the zero value gray so the non-zero values stand out better.

    Conditional formatting does not seem to work in this case.
    I'm using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20702) 32-bit with Windows 10

    I'm trying to apply the conditional formatting to the entire sheet.
    I select all, then use the conditional formatting dialog to create a rule "Format only cells that contain"
    "Cell value equal to"
    I set a gray text as the format.

    I've tried these values for the cell value equal to:

    I enter the character 0, which results in a rule with =0 in the "equal to" field
    I enter the string '0 which results in a rule with ="'0" in the "equal to" field
    I enter the string "0" which results in a rule with =""0"" in the "equal to" field
    I enter the string '0' which results in a rule with ="'0'" in the "equal to" field

    None of these have any effect on the text in the sheet. The color of the text in cells containing the text 0 are unchanged from the default black.

    What is the proper way to match the text value of 0 for conditional formatting?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Conditional formatting based on numeric value stored as text

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Conditional formatting based on numeric value stored as text

    I find using the Cell contains value option with 0 entered works for both text and the number. See attached.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,089

    Re: Conditional formatting based on numeric value stored as text

    Select A2:B7
    Formula for CF

    =AND(ISTEXT(A2),VALUE(A2)=0)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,837

    Re: Conditional formatting based on numeric value stored as text

    If you're wanting to format just those cells that have a 0 as text, you can try this formula in Conditional Formatting:
    =A1=TEXT(A1,"0")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Duplicate Conditional Formatting; Stored value
    By suiathon in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-13-2019, 01:30 PM
  2. Conditional Formatting based on Text
    By DGorgon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2017, 10:42 AM
  3. conditional formatting fill cells based on text, how to find sum based on CF colour
    By tubbybear in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-27-2016, 12:04 PM
  4. Conditional Formatting based on Text and Value
    By Barrera006 in forum Excel General
    Replies: 12
    Last Post: 10-07-2015, 07:34 AM
  5. [SOLVED] Conditional formatting based on a text and a numeric condition
    By coolshooter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2012, 02:56 AM
  6. Formatting Text based on numeric values in the next column
    By Grateful in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-13-2009, 11:10 AM
  7. [SOLVED] Conditional formatting based on text
    By Gilles Desjardins in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1