+ Reply to Thread
Results 1 to 7 of 7

How to conditionally format cells using partial text

  1. #1
    Forum Contributor
    Join Date
    07-30-2014
    Location
    Mosman, Australia
    MS-Off Ver
    2013
    Posts
    118

    How to conditionally format cells using partial text

    Hi there,

    I want to use the conditionally format tool, specifically the one titled "Use a formula to determine what cells to format".

    I have a list of departments and their locations, i want the conditional format to use the cells in column H based on partial text.

    I currently use the following formula:

    Please Login or Register  to view this content.
    It works on the cells that only show '50 Martin Place' however i have other cells that actually show 'Level 2, 50 Martin Place' and other floors. I want it to format these cells too, anything that contains '50 Martin Place' in the cell.

    I use this conditional format as it then highlights the cells on either side of column H, this part i can fix up easily, i just don't know how to write the formula correctly, i tried using "*50 Martin Place*" but it didn't work.

    Many thanks

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    963

    Re: How to conditionally format cells using partial text

    You are just missing a Wild card aka asterisk '*' add that in front to say that your looking for something that ends in a text, Behind it to say it starts with it or in front and behind to say contains this string of characters. That should resolve your issue
    -If you think you are done, Start over - ELeGault

  3. #3
    Forum Contributor
    Join Date
    07-30-2014
    Location
    Mosman, Australia
    MS-Off Ver
    2013
    Posts
    118

    Re: How to conditionally format cells using partial text

    Hi ELeGault,

    Thanks for the information but i already mentioned i had done that and it doesn't work. I placed an asterisk one in front, one behind and one in front and behind and none of those options helped.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to conditionally format cells using partial text

    Try it like this...

    =SEARCH("50 Martin Place",$H1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to conditionally format cells using partial text

    The wildcards, * and ?, only work in certain functions. They won't work in a logical expression as you have tried.

  6. #6
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    963

    Re: How to conditionally format cells using partial text

    Ah right, My apologies, =Countifs($H1,"*51 Martin Place*")>0 completely overlooked that you were doing a straight comparison of texts

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to conditionally format cells using partial text

    Yes, COUNTIF(S) are some of the functions that accept wildcards.

    In this application (conditional formatting) you don't need the test for >0.

    =COUNTIF($H1,"*51 Martin Place*")

    In CF, the format will be applied if the formula evaluates to either TRUE or ANY number other than 0.

    COUNTIF will return either a 1 (the cell contains the string) or 0 (the cell does not contain the string). When the formula result = 1 the format will be applied.

+ 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. [SOLVED] Conditionally Format Exact Text
    By georgedaly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2013, 06:26 AM
  2. Replies: 2
    Last Post: 02-10-2012, 04:32 PM
  3. Conditionally format numbers stored as text
    By jimojimo in forum Excel General
    Replies: 6
    Last Post: 04-28-2010, 11:45 AM
  4. How do I conditionally format only some of the text in a cell?
    By Goo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-10-2006, 10:55 AM
  5. insert text conditionally and format
    By John Sawyer in forum Excel General
    Replies: 3
    Last Post: 08-17-2005, 02:05 PM

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