+ Reply to Thread
Results 1 to 12 of 12

TextJoin formula with MID function

  1. #1
    Registered User
    Join Date
    02-11-2018
    Location
    Germany
    MS-Off Ver
    Office 2007
    Posts
    22

    Post TextJoin formula with MID function

    I tried to run this formula in excel
    Please Login or Register  to view this content.
    but it returns #Value! error. Here in the range EP3:ET3, I have values like (L1,L2,R1,R22,R30) etc. basically alphanumeric values.
    and according to the digital values I try to group letters together with textjoin which I couldn't make it work.

    Can you help me with the formula what is wrong with it, or suggest me an alternative, please? Thanks.
    Attached Files Attached Files
    Last edited by Brsth; 04-25-2018 at 02:04 AM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: TextJoin formula with MID function

    Need to see some data in context.

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    02-11-2018
    Location
    Germany
    MS-Off Ver
    Office 2007
    Posts
    22

    Re: TextJoin formula with MID function

    Ok, now I attached an excel file with some data and the formulas that I tried. thanks.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: TextJoin formula with MID function

    TEXTJOIN() - This feature is only available if you have an Office 365 subscription.
    Last edited by sandy666; 04-25-2018 at 02:39 AM.

  5. #5
    Registered User
    Join Date
    02-11-2018
    Location
    Germany
    MS-Off Ver
    Office 2007
    Posts
    22

    Re: TextJoin formula with MID function

    I have both Excel 2008 and 2010. I use textjoin for other data on Excel 2010 with Textjoin VBA code. and it works. I tried these formulas on Excel 2010.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: TextJoin formula with MID function

    so maybe update excel version(s) on your profile and add 3 letters: Mac
    and say in the first post , your textjoin function is UDF not build-in

    btw. your example file doesn't contain any VBA code with TEXTJOIN
    Last edited by sandy666; 04-25-2018 at 03:10 AM.

  7. #7
    Registered User
    Join Date
    02-11-2018
    Location
    Germany
    MS-Off Ver
    Office 2007
    Posts
    22

    Re: TextJoin formula with MID function

    ok. thanks. is there a way of doing this without textjoin?

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: TextJoin formula with MID function

    =TEXTJOIN(",",TRUE,IF(--MID($EP3:$ET3,2,255)<=6,LEFT($EP3:ET3),""))
    maybe this but I really don't know. I corrected your formula only
    or
    with your textjoin: IF(--MID($A2:$E2,2,1)<=6,LEFT(A2:E2),"") and CSE
    Last edited by sandy666; 04-25-2018 at 04:21 AM.

  9. #9
    Registered User
    Join Date
    02-11-2018
    Location
    Germany
    MS-Off Ver
    Office 2007
    Posts
    22

    Re: TextJoin formula with MID function

    ok, thank you, Sandy666.

  10. #10
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: TextJoin formula with MID function

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    DATA
    FROM 1 - 6
    FROM 7 - 12
    2
    L8 L9 R1 R2 R7 R,R L,L,R
    3
    L18 R5 R7 R8 R12 R L,R,R,R
    4
    L2 L4 S12 S13 R15 L,L S,S,R
    5
    L9 L11 L3 L6 R1 L,L,R L,L
    6
    L15 G2 R5 R9 R11 G,R L,R,R
    7
    L14 L7 L10 L4 R3 L,R L,L,L
    8
    L17 L8 L2 K5 R14 L,K L,L,R
    9
    L14 L17 L18 L2 R12 L L,L,L,R
    10
    L9 L10 D3 R9 R13 D L,L,R,R
    11
    L13 L11 K7 R10 R12 L,L,K,R,R
    12
    L5 R2 K3 R14 R16 L,R,K R,R
    13
    L8 L9 L4 R7 R11 L L,L,R,R
    14
    L18 M11 L2 L5 R11 L,L L,M,R
    15
    L9 L10 F4 R11 R16 F L,L,R,R
    16
    L9 L12 L4 C1 R8 L,C L,L,R
    Sheet: Sheet1

    G2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    H2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  11. #11
    Registered User
    Join Date
    02-11-2018
    Location
    Germany
    MS-Off Ver
    Office 2007
    Posts
    22

    Re: TextJoin formula with MID function

    Thanks a lot, Shukla! It works great! Thanks for your time and help!

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: TextJoin formula with MID function

    Another way if you are open to VBA solution.

    Install this code (by tigeravatar) in the VBA editor and save file as *xlsm macro enabled file. It is a user defined function called Concatall.

    Please Login or Register  to view this content.
    Then array enter this formula in the first row and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In the other column array enter this and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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. Removing duplicates from a TEXTJOIN
    By CKPHarry in forum Excel General
    Replies: 5
    Last Post: 01-02-2020, 05:57 PM
  2. [SOLVED] Converting TEXTJOIN to CONCATENATE
    By mattmccormack1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2018, 05:46 PM
  3. Textjoin sometimes works well and sometimes doesnot
    By leprince2007 in forum Office 365
    Replies: 36
    Last Post: 04-03-2018, 07:11 AM
  4. Textjoin?
    By johandenver in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 10-01-2017, 03:18 PM
  5. TEXTJOIN only specific words
    By thoart in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 07:56 AM
  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