TextJoin formula with MID function

I tried to run this formula in excel

=TEXTJOIN(",",TRUE,IF(MID($EP3:$ET3,2,255<=6),LEFT($EP3:ET3,1),""))

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.

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.

TEXTJOIN() - This feature is only available if you have an Office 365 subscription.

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

=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

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:

=IFERROR(LEFT(SUBSTITUTE(

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)<=6,COLUMN(A2:E2),""),1)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)<=6,COLUMN(A2:E2),""),2)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)<=6,COLUMN(A2:E2),""),3)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)<=6,COLUMN(A2:E2),""),4)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)<=6,COLUMN(A2:E2),""),5)),1),""),",",REPT(" ",99),COUNT(IF(--MID($A2:$E2,2,255)<=6,COLUMN(A2:E2),""))),99),"")

H2

Formula:

=IFERROR(LEFT(SUBSTITUTE(

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)>=7,COLUMN(A2:E2),""),1)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)>=7,COLUMN(A2:E2),""),2)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)>=7,COLUMN(A2:E2),""),3)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)>=7,COLUMN(A2:E2),""),4)),1),"")&","&

IFERROR(LEFT(INDEX(A2:E2,SMALL(IF(--MID($A2:$E2,2,255)>=7,COLUMN(A2:E2),""),5)),1),""),",",REPT(" ",99),COUNT(IF(--MID($A2:$E2,2,255)>=7,COLUMN(A2:E2),""))),99),"")

...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.

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.

'tigeravatar ExcelForum
Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
    
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
    
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty
            If Len(DataIndex) > 0 Then
                'Found the item isn't empty, check if user specified bUnique as True
                If bUnique = True Then
                    'bUnique is true, check if the item has been included in the result yet
                    If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                        'Item has not been included in the result, add item to the result
                        strResult = strResult & "||" & DataIndex
                    End If
                Else
                    'bUnique is not true, add item to the result
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
    
    'Output result
    ConcatAll = strResult
    
End Function

Then array enter this formula in the first row and fill down.

Formula:

=concatall(IF(--MID(A2:E2,2,50)<7,LEFT(A2:E2,1),""),",")

In the other column array enter this and fill down.

Formula:

=concatall(IF((--MID(A2:E2,2,50)>6)*(--MID(A2:E2,2,50)<13),LEFT(A2:E2,1),""),",")