Concatenate cells if they are in a certain range

Question

I am looking for a formula to concatenate cell values (say A1:E1) to G1 if they are in a certain range, like cells values are smaller than < 7, or cell values are between 8 and 15, etc.

I tried to create a formula like this : =concatenate(if(A1:E1<7,"")) which is not correct. I couldn't find similar example on the internet either.

Example:

A1:1 | B1:2 | C1:3 | D1:5 | E1:50  

Concatenate if A1:E1 is smaller than 7 => Result :  G1: (1,2,3,5)
Concatenate if A1:E1 is between 1 and 7 => Result : G1: (2,3,5)
Concatenate if A1:E1 is bigger than 10 => Result :  G1: (50)

How can I achieve this? Thanks!!

Scott Craner Scott Craner · Accepted Answer · 2017-08-21T21:14:03

Use TEXTJOIN as an array formula:

=TEXTJOIN(",",TRUE,IF(A1:E1<=7,A1:E1,""))
=TEXTJOIN(",",TRUE,IF((A1:E1<=7)*(A1:E1>1),A1:E1,""))
=TEXTJOIN(",",TRUE,IF((A1:E1>=10),A1:E1,""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TEXTJOIN was introduced in Office 365 Excel. If you do not have that then it will require vba. Put the following in a module attached to the workbook and use the formula as described above.

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Concatenate cells if they are in a certain range

1 Answers