+ Reply to Thread
Results 1 to 10 of 10

Conditional formatting by matching part characters of cell

  1. #1
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Conditional formatting by matching part characters of cell

    Ref. Attached file TEXT.XLSX

    I have two columns of data. I want to highlight cells if their last five digits are found in another column.

    ie. if cell "B" entry is 23658, and A1 is 10000023658, Cell A1's color should be highlighted.

    How to do this? Thanks in advance.
    Attached Files Attached Files
    Ask me how to hate XL.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Conditional formatting by matching part characters of cell

    Only the last 5? Not the last 4?
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Conditional formatting by matching part characters of cell

    Use this in conditional formatting:
    =ISNUMBER(MATCH(--RIGHT(A1,5),$B$1:$B$10,0))

    Adjust the ranges as needed.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Conditional formatting by matching part characters of cell

    CF formula rule:

    =B1=--RIGHT(A1,5)

  5. #5
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: Conditional formatting by matching part characters of cell

    Quote Originally Posted by Glenn Kennedy View Post
    Use this in conditional formatting:
    =ISNUMBER(MATCH(--RIGHT(A1,5),$B$1:$B$10,0))

    Adjust the ranges as needed.
    Seems to be Ok. But when i apply, i get no solution, as i do something wrong. I reattached another file TEST222.

    In this i am trying to match last 5 digits of SHEET "TWO" with entries of SHEET "ONE". Formatting should be applied to SHEET "TWO".

    PLease help.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Conditional formatting by matching part characters of cell

    Please would you manually highlight some of the matches you are looking for in your sample sheet - say the first 10 or so? Thanks.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Conditional formatting by matching part characters of cell

    This is a different question. You asked for matches in LAST 5 digits in column A, sheet TWO with entries in column a, sheet ONE. Firstly, a start, there are NO values in ONE column A that have 5 digits. Secondly, in your initial unrepresentative example (Sheet1), the values in column A were text and those in column B were numbers. Now BOTH sets of values in sheets ONE and TWO are all text.

    So, I made a guess that you wanted the matches between the last 5 digits from TWO with the last 5 digits of A... in ONE


    Exactly what do you want?
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: Conditional formatting by matching part characters of cell

    Quote Originally Posted by Glenn Kennedy View Post
    This is a different question. You asked for matches in LAST 5 digits in column A, sheet TWO with entries in column a, sheet ONE. Firstly, a start, there are NO values in ONE column A that have 5 digits. Secondly, in your initial unrepresentative example (Sheet1), the values in column A were text and those in column B were numbers. Now BOTH sets of values in sheets ONE and TWO are all text.

    So, I made a guess that you wanted the matches between the last 5 digits from TWO with the last 5 digits of A... in ONE


    Exactly what do you want?
    Thanks a lot, Your guess is right. Anyway, I was in hurry and forced to do it manually..........

  9. #9
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: Conditional formatting by matching part characters of cell

    Quote Originally Posted by AliGW View Post
    Please would you manually highlight some of the matches you are looking for in your sample sheet - say the first 10 or so? Thanks.
    Thanks, Glenn solved it.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Conditional formatting by matching part characters of cell

    You're welcome.

+ 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. Replies: 1
    Last Post: 03-06-2014, 07:14 PM
  2. Replies: 5
    Last Post: 01-14-2013, 01:26 PM
  3. Replies: 2
    Last Post: 10-09-2012, 03:57 PM
  4. conditional formatting based on PART of a cell value
    By agni452 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-02-2012, 05:37 AM
  5. [SOLVED] conditional formatting to count characters in a cell
    By juliemad in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2012, 04:03 PM
  6. Conditional Formatting based on part content of a cell
    By Paul Sheppard in forum Excel General
    Replies: 2
    Last Post: 09-10-2009, 01:06 PM
  7. Conditional Formatting - part of cell only
    By RobDDrums in forum Excel General
    Replies: 1
    Last Post: 01-09-2006, 07:15 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