+ Reply to Thread
Results 1 to 6 of 6

Function similar to textjoin

  1. #1
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Function similar to textjoin

    Hello,

    I am currently using TEXTJOIN as CSE to join comments based on number values in the related cells.
    However, I have experienced some problems using the function, and I was wondering if there was a similar way to get exactly the same result, either by 1 function or a combination of functions.

    I attached a test workbook.
    Attached Files Attached Files

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

    Re: Function similar to textjoin

    =iferror(index($o$3:$aj$3,match(99^99,c3:m3)),"")

  3. #3
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: Function similar to textjoin

    Thanks for your response!
    However, this will only return 1 comment. I need several comments to be joined, separated with commas, like in the test sheet.

  4. #4
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: Function similar to textjoin

    Any suggestions?
    Last edited by Allerdrengen; 11-06-2018 at 01:49 PM.

  5. #5
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Function similar to textjoin

    Maybe,

    1] Put your comments in O2:AA2

    2] In O3, copied across to AA3 and all copied down :

    =TRIM(IF(A3="","",SUBSTITUTE(O$2," ","@"))&" "&P3)

    3] In N3, copied down :

    =SUBSTITUTE(SUBSTITUTE(O3," ",", "),"@"," ")

    4] Hide Column O to Column AA if necessary

    Regards
    Bosco
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    92

    Re: Function similar to textjoin

    Hi, thanks for your response.
    The solution seems very good, but I was wondering if there is a way to tweak the function, so it only returns a number if there is an actual value.
    So it shouldnt do anything if there is a 0 in the cell, or a function in the cell that returns a 0 or a blank cell.
    Thanks!

+ 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. Removing duplicates from a TEXTJOIN
    By CKPHarry in forum Excel General
    Replies: 5
    Last Post: 01-02-2020, 05:57 PM
  2. [SOLVED] TextJoin formula with MID function
    By Brsth in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-26-2018, 11:00 AM
  3. [SOLVED] Converting TEXTJOIN to CONCATENATE
    By mattmccormack1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2018, 05:46 PM
  4. Textjoin sometimes works well and sometimes doesnot
    By leprince2007 in forum Office 365
    Replies: 36
    Last Post: 04-03-2018, 07:11 AM
  5. Textjoin?
    By johandenver in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 10-01-2017, 03:18 PM
  6. If Match then return TEXTJOIN
    By Cynops in forum Excel General
    Replies: 1
    Last Post: 11-09-2016, 07:51 AM
  7. TEXTJOIN function
    By Tony Valko in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2016, 06:48 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