Concatenate Text With a Separator Both Ways in Excel

Putting several values into one piece of text is a small job that comes up everywhere: a full name from three columns, an address on one line, a list of product names per order, tags separated by commas. Both Excel and SQL do it, and both have a trap waiting where the data has gaps. In Excel the trap is a doubled separator when a middle cell is blank. In SQL the trap is a whole result turning NULL because one part was missing. This article covers the two jobs, joining columns and joining rows, in both tools. It is one of the worked pairs in moving between Excel and SQL, and the inverse operation is in splitting text by delimiter in Excel and SQL queries.

Two jobs, not one

Across columns, within a row. First name, middle name and last name become one full name. Street, city and postcode become one address line. The values are in different columns of the same row.

Across rows, within a group. The product names on an order become "Widget, Gadget, Cable". The tags on an article become one list. The values are in the same column in many rows.

Excel's TEXTJOIN serves both, since it accepts any range. SQL separates them: concat_ws for columns, string_agg for rows.

Sample data

Customers with optional middle names, in A2:C5 and in customers(first_name, middle_name, last_name):

And order lines, in Lines and order_lines(order_id, product):

Excel: columns in a row

The ampersand operator joins fixed pieces:

=A2 & " " & B2 & " " & C2

For Ana this gives "Ana Maria Silva". For Ben, whose middle name is blank, it gives "Ben Okafor", with two spaces in the middle. The doubled space is the signature mistake of ampersand joins. TEXTJOIN removes it:

=TEXTJOIN(" ", TRUE, A2:C2)

The first argument is the delimiter, the second says whether to ignore empty cells (TRUE), and the rest are the cells or ranges. For Ben this gives "Ben Okafor". The TRUE is the entire point: with FALSE, empty cells produce adjacent delimiters. CONCAT and the older CONCATENATE join without a delimiter and do not skip blanks, so they are for glueing, not listing.

TEXTJOIN and CONCAT exist from Excel 2019. In older versions, use ampersands and clean up with TRIM, which collapses repeated spaces: =TRIM(A2 & " " & B2 & " " & C2). The trick works for spaces and not for commas.

A cell that holds an empty string from a formula counts as empty to TEXTJOIN with ignore_empty true, which helps when parts come from formulas.

Excel: values from many rows

To list the products of order 1:

=TEXTJOIN(", ", TRUE, FILTER(Lines[product], Lines[order_id]=1, ""))

FILTER selects the rows for the order and TEXTJOIN joins them: "Widget, Gadget, Cable". Wrap the filter in SORT for alphabetical order: TEXTJOIN(", ", TRUE, SORT(FILTER(...))). Wrap it in UNIQUE to remove repeats. For one such cell per order, with the order IDs in F2:F3:

=BYROW(F2:F3, LAMBDA(o, TEXTJOIN(", ", TRUE, FILTER(Lines[product], Lines[order_id]=o, ""))))

This spills a list, one string per order. In versions before dynamic arrays, a similar result needs an array-entered TEXTJOIN(", ", TRUE, IF(range=F2, values, "")).

A cell has a limit of 32,767 characters, and a TEXTJOIN that exceeds it returns #VALUE!. Only the first 1,024 characters of a cell show up in some views and in a formula bar; the limit is practical, not theoretical.

SQL: columns in a row

The standard concatenation operator, ||, behaves badly with missing parts:

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM customers;

For Ana this works. For Ben, middle_name is NULL, and anything concatenated with NULL is NULL, so the whole name is NULL. This is the SQL version of the doubled space, and worse, because it deletes the value. Use concat_ws (concatenate with separator):

SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name
FROM customers;

concat_ws skips NULL arguments and puts the separator only between the ones that remain, giving "Ana Maria Silva", "Ben Okafor" and "Cho Park". The first argument is the separator. Note that concat_ws skips NULL but not empty strings; if blanks arrive as '' rather than NULL, convert them first with nullif(middle_name, ''). concat() also treats NULL as empty but takes no separator. MySQL and SQL Server (2017 and later) have CONCAT_WS too; Oracle's || treats NULL as an empty string, unlike PostgreSQL, which is one of many porting surprises.

SQL: values from many rows

string_agg joins values across the rows of a group:

SELECT order_id,
       string_agg(product, ', ' ORDER BY product) AS products
FROM order_lines
GROUP BY order_id;

Output:

The ORDER BY inside the call controls the order; without it, the order is whatever the database produces, which can differ between runs and versions. Add DISTINCT to remove repeats: string_agg(DISTINCT product, ', ' ORDER BY product) (the DISTINCT expression must match the order expression). string_agg skips NULL values and returns NULL for a group with no non-null values. MySQL's equivalent is GROUP_CONCAT(product ORDER BY product SEPARATOR ', '), with a default result limit of 1,024 characters (group_concat_max_len) that truncates silently. SQL Server (2017 and later) has STRING_AGG(product, ', ') WITHIN GROUP (ORDER BY product).

Separators and ambiguity

A list joined with ", " cannot be split back safely if a value contains a comma. "Cable, 2 m" and "Widget" joined gives "Cable, 2 m, Widget", which looks like three items. Choose a separator that cannot occur in the data (a pipe, a semicolon, a tab, a newline) or put quotes around values before joining. If the joined string is only for human eyes, a comma is fine. If another program will split it, use a safe delimiter or, better, send a proper array or a JSON list (splitting text by delimiter in Excel and SQL queries).

Line breaks as separators work in both tools. In Excel, TEXTJOIN(CHAR(10), TRUE, ...) and turn on Wrap Text in the cell. In SQL, string_agg(product, E'\n') in PostgreSQL. Spreadsheet cells and CSV files with embedded line breaks need quoting, or the row appears to split (escaping quotes and newlines inside CSV).

Types

Joining numbers and dates needs a decision about formatting. In Excel, TEXTJOIN converts a number to text using general formatting, so 1234.5 becomes "1234.5" and a date becomes its serial number. Format first with TEXT: TEXT(A2, "yyyy-mm-dd") and TEXT(B2, "#,##0.00"). In SQL, concat_ws converts its arguments to text using the default output; control the format with to_char. A date joined without formatting uses the session's DateStyle, which varies by connection.

Checking

Check the edge cases: all parts present, one part missing (first, middle and last position), all parts missing, a part containing the separator, a part with leading or trailing spaces, and a very long result. For the group version, check a group with one value, a group with repeated values and a group with NULLs. For the sample: "Ana Maria Silva", "Ben Okafor", "Cho Park", and "Cable, Gadget, Widget" for order 1. Compare the sheet and the query row by row.

When joining text in a cell is the wrong design

Joined text is a presentation: good in a report, a label or an export. It is the wrong design for stored data. A column holding "Widget, Gadget, Cable" cannot be joined, filtered or counted without splitting it, which is the denormalized list that database design teaches against; store one row per product instead and join the text at display time. In a spreadsheet that feeds a database, a tags column of comma-separated values is the common example, and the loader must split it into rows (designing tables from a messy Excel sheet). Microsoft documents TEXTJOIN, and the PostgreSQL manual describes concat_ws and string_agg.