Join text in excel

[icytuvi]

Hi,

I have two tables including the 'Database' and 'Summary' as below. In the 'Summary table' I would like to list out all available value in 'Database' and I am using &", " to join text from the cells.

Could you please help me an alternative formula so that there won't be the comma if the cell is blank? In my version of excel, CONCAT is not available.

Thanks a lot.

Book1.xlsx
	B	C	D	E	F	G	H	I	J
2	Database							Summary
3	Product 1	Product 2	Product 3	Product 4	Product 5	Product 6		Product 1	Alabama, Florida, , , ,
4	Alabama	Hawaii	Iowa			Washington DC.		Product 2	Hawaii, Colorado, , , ,
5	Florida	Colorado				Hawaii		Product 3	Iowa, , , , ,
6						Colorado		Product 4	, , , , ,
7						Iowa		Product 5	, , , , ,
8						Maryland		Product 6	Washington DC., Hawaii, Colorado, Iowa, Maryland, Oregon
9						Oregon
Sheet1

Cell Formulas
Range	Formula
J3	J3	=B4&", "&B5&", "&B6&", "&B7&", "&B8&", "&B9
J4	J4	=C4&", "&C5&", "&C6&", "&C7&", "&C8&", "&C9
J5	J5	=D4&", "&D5&", "&D6&", "&D7&", "&D8&", "&D9
J6	J6	=E4&", "&E5&", "&E6&", "&E7&", "&E8&", "&E9
J7	J7	=F4&", "&F5&", "&F6&", "&F7&", "&F8&", "&F9
J8	J8	=G4&", "&G5&", "&G6&", "&G7&", "&G8&", "&G9

 

[FormR]

In my version of excel, CONCAT is not available.

It might be time for an upgrade this is trivial in the modern versions of Excel - but for J3 you could try:

=MID(IF(B4="",""," ,"&B4)&IF(B5="",""," ,"&B5)&IF(B6="",""," ,"&B6)&IF(B7="",""," ,"&B7)&IF(B8="",""," ,"&B8)&IF(B9="",""," ,"&B9),3,9999)

 

[JEC]

Use Power Query. You may have to install Power Query as an Add-in in office 2013.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unPiv = Table.UnpivotOtherColumns(Source, {}, "Product", "Value"),
    grp = Table.Group(unPiv, {"Product"}, {{"Combined", each Text.Combine([Value],", ")}})
in
    grp

 

[icytuvi]

It might be time for an upgrade this is trivial in the modern versions of Excel - but for J3 you could try:

=MID(IF(B4="",""," ,"&B4)&IF(B5="",""," ,"&B5)&IF(B6="",""," ,"&B6)&IF(B7="",""," ,"&B7)&IF(B8="",""," ,"&B8)&IF(B9="",""," ,"&B9),3,9999)

Thank you very much, it works perfectly for me! I am using company's laptop and it's kinda difficult to ask them to upgrade

 

[icytuvi]

Use Power Query. You may have to install Power Query as an Add-in in office 2013.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unPiv = Table.UnpivotOtherColumns(Source, {}, "Product", "Value"),
    grp = Table.Group(unPiv, {"Product"}, {{"Combined", each Text.Combine([Value],", ")}})
in
    grp

View attachment 111803

Thank you very much, I'll try with your suggestion

 

[FormR]

I am using company's laptop

That's even crazier as Office 2013 is no longer supported
End of support for Office 2013 - Microsoft Support.

 

[myall_blues]

As yet another alternative, many years ago when TEXTJOIN appeared and our work versions hadn’t caught up I wrote some VBA code to create a user defined function to do the same thing.
You could try this code. It will accept a range, a single cell reference, a literal string value or an array of values.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], [....], etc)
e.g. =TEXTJOIN(" ",FALSE,A1:A3,B1)
=TEXTJOIN(" ",FALSE,"whippets",B2)
=TEXTJOIN(" ",FALSE,{"I","love","whippets"},"a","lot")

All text arguments except text 1 are optional.
ignore_empty is boolean (TRUE/FALSE)

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray textn() As Variant) As String
    Dim i
    Dim rng
    For Each rng In textn
        If IsObject(rng) Or IsArray(rng) Then
            For Each i In rng
                If Len(i) = 0 Then
                    If Not ignore_empty Then
                        TEXTJOIN = TEXTJOIN & i & delimiter
                    End If
                Else
                    TEXTJOIN = TEXTJOIN & i & delimiter
                End If
            Next
        Else
            If Len(rng) = 0 Then
                If Not ignore_empty Then
                    TEXTJOIN = TEXTJOIN & rng & delimiter
                End If
            Else
                TEXTJOIN = TEXTJOIN & rng & delimiter
            End If
        End If
    Next
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - 1)
End Function

 

[icytuvi]

That's even crazier as Office 2013 is no longer supported
End of support for Office 2013 - Microsoft Support.

now I am using Office 2016 just not updated in the profile yet

 

[icytuvi]

As yet another alternative, many years ago when TEXTJOIN appeared and our work versions hadn’t caught up I wrote some VBA code to create a user defined function to do the same thing.
You could try this code. It will accept a range, a single cell reference, a literal string value or an array of values.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], [....], etc)
e.g. =TEXTJOIN(" ",FALSE,A1:A3,B1)
=TEXTJOIN(" ",FALSE,"whippets",B2)
=TEXTJOIN(" ",FALSE,{"I","love","whippets"},"a","lot")

All text arguments except text 1 are optional.
ignore_empty is boolean (TRUE/FALSE)

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray textn() As Variant) As String
    Dim i
    Dim rng
    For Each rng In textn
        If IsObject(rng) Or IsArray(rng) Then
            For Each i In rng
                If Len(i) = 0 Then
                    If Not ignore_empty Then
                        TEXTJOIN = TEXTJOIN & i & delimiter
                    End If
                Else
                    TEXTJOIN = TEXTJOIN & i & delimiter
                End If
            Next
        Else
            If Len(rng) = 0 Then
                If Not ignore_empty Then
                    TEXTJOIN = TEXTJOIN & rng & delimiter
                End If
            Else
                TEXTJOIN = TEXTJOIN & rng & delimiter
            End If
        End If
    Next
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - 1)
End Function

Thank you very much

 

[Burrgogi]

OP, I have been waiting to post this because I wanted you to get your answer first without distraction. I have to say that I love your forum avatar. Is he/she yours? He's very cute!

I have been a lifelong dog lover. A few years ago, I had a new neighbor move in next door to me. I got to know him and his family. They had an orange cat with a cute little personality. I never liked cats to begin with but he completely won me over. I'm officially a dog AND a cat person now. So much so, that I plan on adopting a cat someday. I miss Duke (cat) a lot!

Could you please help me an alternative formula so that there won't be the comma if the cell is blank? In my version of excel, CONCAT is not available.

By the way, I am using a version of MS Office older than yours and the =CONCATENATE function is definitely available. I suspect you have it as well. You probably just missed it.