Comments on: How to compare two cells in Excel (string comparison)

While Excel formulas are always perfectly true, their results may be wrong because some flawed data penetrated into the system. This tutorial will teach you how to automate the tedious and error-prone task of cell comparison and what formulas are best to use in each particular case. Continue reading →

Comments page 2. Total comments: 63

  1. how to prepare a database and then matching a digit to the database

  2. Hi,

    I would like to ask if there is a formula or way we can count the number of words in a text in a cell and compare it to another cell and check if what words does not match.

    I hope you read this comment and answer. Thanks you!!

  3. Hello Svetlana,

    Could you teach following please:

    If column A has a word or sentence
    And column B has another word or sentence

    How can column C tell if column A and column B has at least a X equal letters in sequence that are the same, disconsidering capital or lower case sensitive

    Example:

    Cell A1 has word "daily"
    Cell B1 has word "day"

    So if X=2, means it will compare if at least 2 same letters in sequence appear in both cells of the same line.

    In this case answer is yes because "daily" in cell A1 has the letters "da" and "day" in cell B1 also has the letters "da".

    But in this same case if we program X=3, then it will come as No Equal 3 letters in a sequence, because "dai" is different than "day".

    If you could help which formula to use would be greatly appreciate.

    Thank you

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  4. Hi Svetlana,

    The training lesson is very useful. great help indeed. Thank you for putting up such wonderful lessons.

  5. If B2 is than or = to 100% of D2 I want the color to be green.
    For the life of me and much time I can't figure this conditional format out.

    Please help!

  6. I'm looking for comparision of two cells by ignoring return chars.
    For ex. when we compare below 2 values, it should return true.
    I saw Exact, Using IF, TRIM but couldn't find how to ignore return chars

    1. Hello World
    2. Hello
    World

  7. Hi Svetlana, I have data in three columns in the pivot. Please see row 4 and 5, they are the same record but because of comma it is showing up in two different lines, is there is any fast way to clean such data?

    GENEVA, TOWN OF-WTP NY 14456
    GENOA, CITY OF IL 60135
    GEORGE, CITY OF IA 51237
    4.GEORGETOWN TOWN OF DE 19947
    GEORGETOWN, CITY OF SC 29440
    5.GEORGETOWN, TOWN OF DE 19947

  8. Hi

    I wanted to give marks in a question according to the following scheme. It's a multiple choice problem with more than one option possible. There are 4 options. If a student gets it completely correct, he/she gets 4 marks. If he/she gets it partially correct, then marks are given according to the number of options which are correct for the student. But if even one option given is wrong, then -2 marks are awarded.
    Ex: Let the answer be ABC. Then marks awarded according to answer given:
    A: 1; B: 1; AB: 2; ABC: 4; D: -2; ABD: -2

    How can we do its programming in excel?
    Thanks
    MS

  9. Hi

    I wanted to give marks in a question according to the following scheme. It's a multiple choice problem with more than one option possible. There are 4 options. If a student gets it completely correct, he/she gets 4 marks. If he/she gets it partially correct, then marks are given according to the number of options which are correct for the student. But if even one option given is wrong, then -2 marks are awarded.
    Ex: Let the answer be ABC. Then marks awarded according to answer given:
    A: 1; B: 1; AB: 2; ABC: 4; D: -2; ABD: -2

    How can we do its programming in excel?
    Thanks

  10. i want to manage my stock i given to dealer A 3 mobile which serial 123,124,125 and give 3 mobile to another dealer B which serial 126,127,128 now if dealer A sold 126 serial mobile than value return with false and if he sold 123 than value show as true because 123 serial is in stock of dealer A

  11. Hi Svetlana,
    I am trying to make a daily working record for our project. I need to have four column "time-in"; "time-out", "Total hours worked", "Overtime calculation". I can't seem to format the time-in , time-out cell to format such an 1.30am. For though I am selecting the option; when I enter the time after formatting the cells it shows the following format 1/8/1900 12:00:00 AM. By the way I am using MS Office 2016.
    Thank you
    Nafisa

  12. Hi Svetlana,

    I have columns of information as follows.
    A:F
    Row 1: A B C X X X
    Row 2: A B X X X X
    H:M
    Row 1: E F G X X X
    Row 2: E F X X X X

    I want P:U to look as below.
    Row 1: A B C D E F G
    Row 2: A B E F

    I have more than two thousand rows like this.
    Please help me.

    Regards,
    Jeb Singh

  13. Plz solving hlookup formula and send my email hlookup sheet

  14. hi Svetlana,
    first of all, its highly appciable what you are doing for a newcomer in excel like me. i read all of your post but couldn't find what i am looking for so here I am giving details of my query and hope you will reply as you get time.

    A B Result
    0.481 0.25 High or 0.23 down sign
    0.618 0 Less

    Is it possible to use formulae to compare the two column and based on the column A shows the diffrence with color higlight and symbol of up and down.

    thanks

    1. Hi IRSHAD,

      You can calculate the difference with this simple formula: =B2-A2

      Then, enter the following formula in column D. It returns 2 if B2 is greater then A2, 1 if B2 is equal to A2, and 0 if B2 is less than A2:

      =IF(B2>A2, 2, IF(B2<A2, 0, 1))

      Finally, create an icon set rule for column D like shown in the screenshot below. Please pay attention that the "Show icon only" box should be selected:

      Highlight cell difference

  15. Your posts are awesome and explanatory enough that even a novice can understand typical formulas.

  16. This site is great. I was wondering if you could start posting some blogs about VBA coding and automating standard work tasks to save time.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)