+ Reply to Thread
Results 1 to 9 of 9

Extract All Remaining Text

  1. #1
    Registered User
    Join Date
    05-09-2013
    Location
    Miami, Florida
    MS-Off Ver
    Excel 2019 (online)
    Posts
    10

    Extract All Remaining Text

    I have users input their preferences by checking certain fruits they would like to have. There are columns for all the fruit options that has a formula attached to create a

    if the individual chose it. If someone has a suggestion I would like for the non fruit items to go into the "Other" Column. I need help extracting only the remaining text excluding the text which already has options in columns (C - G ), if the individual selected it. Is there a way to extract the other text?

    A B C D E F G H
    1 person name selection / other cherries strawberries grapes oranges mangoes Other
    2 Jill cherries, grapes, mangoes, Almonds, cottage cheese trying to get formula to solve and return only Almonds, cottage cheese since it is not one of the options in Columns (C - G)
    3 Kirk strawberries, grapes, trail mix trying to get formula to solve and return only trail mix since it is not one of the options in Columns (C - G)
    4 Nancy cherries, plain coconut milk yogurt trying to get formula to solve and return only plain coconut milk yogurt since it is not one of the options in Columns (C - G)
    5 Joe grapes, oranges, limes or lemons trying to get formula to solve and return only limes or lemons since it is not one of the options in Columns (C - G)
    6 Larry cherries, strawberries, grapes, oranges, mangoes, maple syrup trying to get formula to solve and return only maple syrup since it is not one of the options in Columns (C - G)
    7 Jane strawberries, mangoes, coffee, creamer, sugar trying to get formula to solve and return only coffee, creamer, sugar since it is not one of the options in Columns (C - G)
    8

  2. #2
    Registered User
    Join Date
    05-09-2013
    Location
    Miami, Florida
    MS-Off Ver
    Excel 2019 (online)
    Posts
    10

    Re: Extract All Remaining Text

    The formula for C2 is:

    =IF(ISNUMBER(SEARCH(C$1, $B2)), "✓", "")

  3. #3
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Extract All Remaining Text

    If there are only going to be 5 boxes with check marks, this task should be achievable with nested SUBSTITUTE functions. I used the following in H2, filled down:

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(", "&$B2,IF($C2="ü",", "&$C$1,""),""),IF($D2="ü",", "&$D$1,""),""),IF($E2="ü",", "&$E$1,""),""),IF($F2="ü",", "&$F$1,""),""),IF($G2="ü",", "&$G$1,""),""),2,10000))

    Note that the check mark symbol in my attachment is an umlaut on a "u" in the wingdings font. If it's different for you, change the five umlauted "u" characters in the formula to whatever your check mark is. Take a look at the attachment to see if it'll do:
    Attached Files Attached Files
    Last edited by CAntosh; 09-22-2017 at 01:04 PM.
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  4. #4
    Registered User
    Join Date
    05-09-2013
    Location
    Miami, Florida
    MS-Off Ver
    Excel 2019 (online)
    Posts
    10

    Re: Extract All Remaining Text

    Thank you so much for helping me. I truly appreciate it! It worked!!!


    Quote Originally Posted by CAntosh View Post
    If there are only going to be 5 boxes with check marks, this task should be achievable with nested SUBSTITUTE functions. I used the following in H2, filled down:

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(", "&$B2,IF($C2="ü",", "&$C$1,""),""),IF($D2="ü",", "&$D$1,""),""),IF($E2="ü",", "&$E$1,""),""),IF($F2="ü",", "&$F$1,""),""),IF($G2="ü",", "&$G$1,""),""),2,10000))

    Note that the check mark symbol in my attachment is an umlaut on a "u" in the wingdings font. If it's different for you, change the five umlauted "u" characters in the formula to whatever your check mark is. Take a look at the attachment to see if it'll do:
    Last edited by alexanderears; 09-22-2017 at 02:14 PM.

  5. #5
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Extract All Remaining Text

    Glad I could help, good luck!

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract All Remaining Text

    Enter formula in H2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G H
    1 person selection cherries strawberries grapes oranges mangoes other
    2 jill cherries, grapes, mangoes, almonds, cottage cheese ü ü ü almonds, cottage cheese
    3 kirk strawberries, grapes, trail mix ü ü trail mix
    4 nancy cherries, plain coconut milk yogurt ü plain coconut milk yogurt
    5 joe grapes, oranges, limes or lemons ü ü limes or lemons
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Extract All Remaining Text

    I thought "✓"is "a" Marlett font

  8. #8
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Extract All Remaining Text

    There's a Marlett version, as well. I know of a few in Wingdings, one in Webdings, at least one in Marlett, and I'm sure there are a few others.

  9. #9
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Extract All Remaining Text

    all fonts in the attached
    marlett is the best
    Attached Files Attached Files

+ 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] Help required - User input for text search and delete remaining rows
    By ssss2005 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-20-2015, 11:06 AM
  2. Replies: 4
    Last Post: 08-13-2014, 11:03 PM
  3. [SOLVED] Extract text from a given point in a text string, when data points do not share the given
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2014, 03:57 PM
  4. Replies: 12
    Last Post: 04-08-2014, 08:12 PM
  5. Taking remaining value and sharing across remaining months?
    By kickme93 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2013, 08:54 AM
  6. [SOLVED] Extract text from a string of text (amend formula to include new criteria)
    By robertguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 04:53 PM
  7. [SOLVED] Remove Text from Cell with Mixed Characters, Sum Remaining Numbers
    By eugeniusjr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-28-2013, 12:08 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