+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting double space intermittently working

Hybrid View

  1. #1
    Registered User
    Join Date
    12-29-2010
    Location
    St. Louis
    MS-Off Ver
    Excel 2007
    Posts
    11

    Conditional formatting double space intermittently working

    I have 2010

    I have conditional formatting that looks for special characters that are listed on another sheet and fills color so the data entry person knows there are special characters that aren't allowed. We also look for double spaces.

    Conditional formatting formula:

    =SUM(--(ISNUMBER(FIND('Special Characters'!$B$2:$B$168,A2))))

    It catches some double spacing where ever it exists in a paragraph. Other times in other cells it doesn't. If I just enter a double space into a cell where it DIDN'T catch it in a paragraph, it catches it.

    Thanks!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting double space intermittently working

    Are you sure they were regular double spaces and not special codes that create non-printable characters, like Char(10) which is created if you hold the Alt key and hit Enter, in order to wrap text?

    also, you don't need the SUM or -- in this formula:

    ISNUMBER(FIND('Special Characters'!$B$2:$B$168,A2))

    or even:

    FIND('Special Characters'!$B$2:$B$168,A2).

    Also try

    SEARCH('Special Characters'!$B$2:$B$168,A2) for non-case sensitive search.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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