+ Reply to Thread
Results 1 to 6 of 6

How to return unique cells, if it contains "text"

  1. #1
    Registered User
    Join Date
    03-28-2022
    Location
    Brabant, The Netherlands
    MS-Off Ver
    MS Office 365
    Posts
    2

    How to return unique cells, if it contains "text"

    Hi all!

    I am looking for a formula that returns the unique cells from a range, under the condition that it contains a certain text.
    The cells do contain more text than the criteria alone.
    For example:
    Screenshot 2022-03-28 154326.png

    The desired formula should return the cells that contain (X) as part of the text in the cell.

    Hope someone is able to help me!

    Best,
    Flexcel_
    Last edited by Flexcel_; 03-28-2022 at 10:03 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: How to return unique cells, if it contains "text"

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: How to return unique cells, if it contains "text"

    I fear the x will not reflect reality and fail but
    =UNIQUE(FILTER(A1:A12,ISNUMBER(SEARCH("(x)",A1:A12,1))))

    (x) could be in a cell rather than hard coded

  4. #4
    Registered User
    Join Date
    03-28-2022
    Location
    Brabant, The Netherlands
    MS-Off Ver
    MS Office 365
    Posts
    2

    Re: How to return unique cells, if it contains "text"

    Ah, that is perfect, I struggled with the addition of the Find command.
    Thank you very much!

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: How to return unique cells, if it contains "text"

    Into B2:

    Please Login or Register  to view this content.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: How to return unique cells, if it contains "text"

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. countif to return "x" for unique & "xx" for duplicate values in a column
    By Shruder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2018, 05:31 PM
  2. [SOLVED] Return "text" from multiple cells to a list with INDEX
    By Gyroko in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2018, 09:58 AM
  3. IF "text" present with valueA >0, return valueB matching "text"
    By Raffen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2015, 10:06 AM
  4. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  5. Replies: 4
    Last Post: 10-05-2013, 10:11 AM
  6. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  7. [SOLVED] Find multiple "text" criteria and return as ""Yes" in Matrix
    By bertrand82 in forum Excel General
    Replies: 11
    Last Post: 04-30-2012, 09:20 AM

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