+ Reply to Thread
Results 1 to 7 of 7

Compare 2 cell and return missing characters

  1. #1
    Registered User
    Join Date
    06-21-2021
    Location
    Singapore
    MS-Off Ver
    16
    Posts
    7

    Post Compare 2 cell and return missing characters

    Is is possible to achieve the below with any formula?

    A1=Mangobanana
    B1=Mango
    C1 (with Formula) = banana

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Compare 2 cell and return missing characters

    Hi,

    This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

    We need to understand whether A1 always starts with the B1 characters. I suspect it's not as simple as that.

    If it does then a simple

    =SUBSTITUTE(A1,B1,"")
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-21-2021
    Location
    Singapore
    MS-Off Ver
    16
    Posts
    7

    Re: Compare 2 cell and return missing characters

    Thank you Richard, this is exactly what I was looking for. Have a good day & stay safe!

  4. #4
    Registered User
    Join Date
    06-21-2021
    Location
    Singapore
    MS-Off Ver
    16
    Posts
    7

    Re: Compare 2 cell and return missing characters

    Hi Richard,

    Another question, I noticed in my worksheet that some of the B1 cells are blank but I know what data needs to be there. Is there any formula where I can get the same result but instead of comparing it with B1, it will be compared with a range of cells where I can manually enter my dynamic data. I have created a sample workbook as attached. Thanks for all your help.
    Attached Files Attached Files

  5. #5
    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,369

    Re: Compare 2 cell and return missing characters

    This is a new question, so start a new thread with a suitable title, please.
    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.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Compare 2 cell and return missing characters

    Its a same question. just clarifying new condition.

    Try:

    =SUBSTITUTE(A2,LOOKUP(2,1/ISNUMBER(SEARCH($B$2:$B$17,A2)),$B$2:$B$17),"")
    Quang PT

  7. #7
    Registered User
    Join Date
    06-21-2021
    Location
    Singapore
    MS-Off Ver
    16
    Posts
    7

    Re: Compare 2 cell and return missing characters

    Thank you so much. Have a good day!

+ 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. How to compare to a list and return missing list items in summary. (if/and)
    By bigtunelover in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-07-2020, 02:06 AM
  2. [SOLVED] Compare two columns on separate sheet, if missing, insert missing data
    By lilvictorians in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-03-2019, 11:44 PM
  3. [SOLVED] Compare two arrays(contains multiple columns and rows) and return the missing
    By leprince2007 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-22-2018, 04:52 PM
  4. [SOLVED] Compare first 4 characters and if matched extract information from cell
    By valentin.nicolae in forum Excel Formulas & Functions
    Replies: 35
    Last Post: 03-18-2017, 09:01 AM
  5. Compare characters of two cells and return the result
    By shahid6634 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-27-2012, 08:43 AM
  6. compare two files and return missing cell contents
    By anstasija in forum Excel General
    Replies: 2
    Last Post: 08-16-2011, 09:45 AM
  7. Compare for dupes and add missing cell info
    By grouchmax in forum Excel General
    Replies: 3
    Last Post: 05-08-2009, 03:17 PM

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