+ Reply to Thread
Results 1 to 11 of 11

Modify formula so that whole cell contents with spaces is combined in search

  1. #1
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Question Modify formula so that whole cell contents with spaces is combined in search

    Hi.

    I have a function which looks at the value in column A (band names) and then returns and output to column C (genre) based on a search of another sheet (Genre_categories) in the workbook.

    The problem I'm having is that the search function is looking for individual words rather than the whole band name. So 'Pink Floyd' in Cell A2 results in a search for 'Pink' and 'Floyd' and returns 'Pop' and 'Rock' as a genre (eg: pop,rock).

    So how can I modify the search to make sure it looks only for the 'whole' band name?

    This if my formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Try it like this:

    =TEXTJOIN(", ",,IF(ISNUMBER(MATCH(A2,Genre_categories!$B$2:$B$333,0)),Genre_categories!$I$2:$I$333,""))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Hi Pete_UK; thanks for this. When I try it I get every cell value from 'Genre_categories column I' entered into the cell.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Hi,

    I think that should be:

    =TEXTJOIN(", ",,IF(Genre_categories!$B$2:$B$333=A2,Genre_categories!$I$2:$I$333,""))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Thanks XOR LX for offering a solution but your formula produces a #VALUE! when it gets to the bold part =TEXTJOIN(", ",,IF(Genre_categories!$B$2:$B$333=A2,Genre_categories!$I$2:$I$333,""))

  6. #6
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Am I asking too much of Excel?

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Modify formula so that whole cell contents with spaces is combined in search

    I don't understand how you could receive a #VALUE! error using the formula I posted. Can you post a workbook (confidential data replaced with dummy data, if necessary)?

    Regards

  8. #8
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Modify formula so that whole cell contents with spaces is combined in search

    I'm happy to post my workbook but the paperclip icon doesn't do anything on my screen. Do I need to add the file somewhere else first?

  9. #9
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Modify formula so that whole cell contents with spaces is combined in search

    so, finally got asn attachment to work! yay! Have a look :-)
    Attached Files Attached Files

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Thanks.

    No errors for me when I open that file.

    However, if you're seeing #VALUE! errors then this could be related to the (apparently undocumented) bug associated with TEXTJOIN, which I myself have witnessed on occasion.

    Sometimes a simple action within the workbook, e.g. filtering/unfiltering, can remedy the situation, in that the TEXTJOIN formulas then cease to display #VALUE! and instead display the correct results. However, in my experience this does not in any way mean that the errors will not return, e.g. upon next opening the workbook.

    Might also be worth checking you're not set to Manual Calculation mode.

    Other than that, I'm afraid I can't help you any further. On a (slightly) positive note, your experiencing this issue means that it's not an issue with my machine, which confirms my suspicions that this is indeed a bug with the TEXTJOIN function. Perhaps I'll attempt to raise it with the Microsoft Tech Team.

    Regards

  11. #11
    Registered User
    Join Date
    11-09-2009
    Location
    Shropshire UK
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Modify formula so that whole cell contents with spaces is combined in search

    Yay! You were absolutely right XOR LX, it worked perfectly when I deleted the column and then added the formula again.

+ 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: 16
    Last Post: 01-06-2016, 06:04 PM
  2. Formula to search/display PDF file from cell contents
    By CampusLibrarian in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-16-2015, 03:27 PM
  3. Modify this Search Formula to search across a range of cells
    By zicitron in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2013, 08:11 AM
  4. Search for different cell contents then modify
    By gspreeman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-28-2013, 10:05 AM
  5. [SOLVED] Combine Cell Contents to with a [return] or [fill with spaces] between each cells contents
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2012, 11:24 PM
  6. Modify code VBA to copy contents only, not formula
    By Mayweed in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2010, 09:36 AM
  7. [SOLVED] Modify cell contents
    By oldguywithbadeyes in forum Excel General
    Replies: 2
    Last Post: 05-02-2006, 05:25 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