I have a formula in EXCEL that concatenates a whole column of data into one big comma separated string.

I have data that looks like the below but I need the LAST comma to disappear or else I get a syntax error. What do I do?

My concatenate function is the following:

="'"&CONCATENATE(TRANSPOSE(L6:L43)&"',")

So what it's doing is EX:

I have a parts list in a column: SB-12-073, SB-18-88, SB-11-001 and I want them concatenated to say 'SB-12-073', 'SB-18-88', 'SB-11-001' but the SB-11-001 needs to have NO COMMA. Currently the concatenate function is pulling the comma over. The data is stored between L6 and L43

CodePudding user response：

The easiest way to concatenate string is using the TEXTJOIN() worksheet function, as in this example:

=TEXTJOIN(",",FALSE,A1:C1)

Here, the comma is used a delimiter and FALSE mentions how to deal with empty cells. As you see, you don't need to add a comma at the end, so your problem won't appear.

CodePudding user response：

You can wrapped around like this to get rid of the last character:

=LEFT(A1;LEN(A1)-1)

cell A1 can be your formula instead.