Join text in excel

icytuvi · Thursday at 4:39 AM

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 · Thursday at 5:15 AM

icytuvi said:
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:

Excel Formula:

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

JEC · Thursday at 5:19 AM

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

Power Query:

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 · Thursday at 5:29 AM

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

Excel Formula:

=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 · Thursday at 5:30 AM

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

Power Query:

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 · Thursday at 5:33 AM

icytuvi said:
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 · Thursday at 5:46 AM

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)

VBA Code:

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 · Friday at 8:59 PM

FormR said:
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 · Friday at 8:59 PM

myall_blues said:
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)

VBA Code:

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 · Friday at 9:47 PM

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!

icytuvi said:
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.

Join text in excel

icytuvi

New Member

Excel Facts

FormR

MrExcel MVP

JEC

Well-known Member

icytuvi

New Member

icytuvi

New Member

FormR

MrExcel MVP

myall_blues

Board Regular

icytuvi

New Member

icytuvi

New Member

Burrgogi

Active Member

Similar threads

Forum statistics

Share this page

Join text in excel

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

New Member

MrExcel MVP

Board Regular

New Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock