Concatenate a long list

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,810
Office Version
  1. 365
Platform
  1. Windows
I have a Table that will vary in size each time the workbook is used. In the first column of the table (ItemID), the user enters Part Numbers. I need to concatenate each part number with a "," in between each (no spaces). Is there a formula that can be used that adjusts to the size of the table. Sometimes the table is 10 rows sometimes over 100 rows.
Thanks for the help - very much appreciate your time.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
675
Office Version
  1. 365
Platform
  1. Windows
Assuming you have Office 365, you can use the formula =TEXTJOIN

For my example, I put "Test" and the row number for column A. In column B, the formula is

Code:
=TEXTJOIN(",",1,A:A)

Snag_6a801180.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with Power Query aka Get&Transform

PartNumbersPartNumbers
885885,726,528,191,337,670,448,859,968,328,493,196,691,882,647,121
726
528
191
337
670
448
859
968
328
493
196
691
882
647
121

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp = Table.Group(Table.AddColumn(Source, "Custom", each 1), {"Custom"}, {{"Count", each _, type table}}),
    Extract = Table.TransformColumns(Table.AddColumn(Grp, "PartNumbers", each [Count][PartNumbers]), {"PartNumbers", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    TSC = Table.SelectColumns(Extract,{"PartNumbers"})
in
    TSC
and NO, this is NOT vba
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
@gheyman
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Assuming you have Office 365, you can use the formula =TEXTJOIN
Office 365 or Excel 2019.


I have a Table
If that is a formal table then the following (adjust for correct table name) will automatically adjust to the size of the table.
If it is not a formal table and you have TEXTJOIN, why not make it into a formal table?

gheyman 2020-08-06 1.xlsm
ABCDE
1ItemIDHdr 2Hdr 3Id1,Id2,Id3,Id6,Id7
2Id146
3Id256
4Id365
560
675
7Id694
8Id773
9
10
Sheet1
Cell Formulas
RangeFormula
E1E1=TEXTJOIN(",",1,Table1[ItemID])
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,810
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Office 2016. Profile has been updated. Thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Profile has been updated.
Thanks. (y)

Another option would be this short user-defined function (post back if you need help with implementation).

VBA Code:
Function IDList(r As Range) As String
  IDList = Replace(Join(Filter(Split("|" & Join(Application.Transpose(r), "|#|") & "|", "#"), "||", False), ","), "|", "")
End Function

You didn't confirm whether or not your table was a formal table so this time I have assumed not.
You said you may have say about 10 to 100 rows. If you allow for say 1000 as I have done below that should allow sufficiently for expansion.

gheyman 2020-08-06 1.xlsm
ABCDE
1ItemIDHdr 2Hdr 3Id1,Id2,Id3,Id6,Id7
2Id146
3Id256
4Id365
560
675
7Id694
8Id773
9
10
Sheet2
Cell Formulas
RangeFormula
E1E1=IDList(A2:A1000)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,388
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top