Replace one delimiter with another

In this example, the goal is to replace the comma separated values in column B with the line break separated values seen in column D. In a problem like this, the first step is to identify the delimiter, which is the character (or characters), that separate each value we want to process. In this case, the values in column B are separated by commas, so a comma (",") is the delimiter. Note that a space sometimes appears with the comma, but it is not consistent. This means we also need to handle this variation.

Note: This example focuses on replacing commas with line breaks, you can use the same approach to replace other delimiters.

 Approaches

There are three basic approaches to solving this problem:

1. Use TEXTSPLIT and TEXTJOIN as shown (Excel 365)
2. Use the SUBSTITUTE function (Legacy Excel)
3. Use Excel's Text-to-Columns feature (any version)

CHAR function

Both formula options below depend on the CHAR function to insert a line break. The CHAR function returns a character when given a valid ASCII code page number. For example:

=CHAR(65) // returns "A"
=CHAR(97) // returns "a"

CHAR can be useful when you want to insert characters in formulas or functions that are awkward or impossible to type directly. For example, you can use CHAR(10) to insert a line break in a formula like this:

="text"&CHAR(10)&"text" // add line break

Note: Text wrap must be enabled to see the line break take effect. 

TEXTSPLIT with TEXTJOIN

If you are using the current version of Excel, the easiest solution is to use the TEXTSPLIT function with the TEXTJOIN function as shown in the worksheet above:

=TEXTJOIN(CHAR(10),TRUE,TRIM(TEXTSPLIT(B5,",")))

Working from the inside out, the first step is to parse the source data by the old delimiter, which is a comma. This is done with the TEXTSPLIT function and the TRIM function like this:

TRIM(TEXTSPLIT(B5,","))

Although TEXTSPLIT can accept up to six arguments, we only need to provide the first two arguments, text and col_delimiter. Text is provided as cell B5, and col_delimiter is a comma (","):

=TEXTSPLIT(B5,",")

The result from TEXTSPLIT is returned to TRIM, which removes any leading or trailing spaces:

TRIM({"Red"," Blue","Green"}) // remove extra space

The result from TRIM is returned directly to the TEXTJOIN function:

=TEXTJOIN(CHAR(10),TRUE,{"Red","Blue","Green"})

Inside TEXTJOIN, delimiter is set to CHAR(10), which is the line break character in Excel. Ignore_empty is set to TRUE in case any values in the original source text are empty (i.e. two commas appear together without a value in between), and text1 is delivered by the TRIM function in the previous step. TEXTJOIN then joins the values in the array with line breaks and returns the result to cell D5.

Note that the cells in the range D5:D8 must have text wrap enabled in order for the line breaks to behave correctly. You can enable Text wrap on the home tab of the ribbon in the Alignment group. Or, you can display Format cells with the shortcut Control + 1 then enable text wrap on the Alignment tab.

Sort option

Because we are working with the TEXTSPLIT function, we can easily sort the values before they are written out with the new delimiter. This is because TEXTSPLIT returns an array of values. The trick is to place the SORT function outside the TRIM function and configure TEXTSPLIT to split text into rows instead of columns:

=TEXTJOIN(CHAR(10),TRUE,SORT(TRIM(TEXTSPLIT(B5,,","))))

We split text into rows because by default SORT will sort by rows, not columns. Another option would be to change the configuration of SORT to sort by columns instead of changing TEXTSPLIT:

=TEXTJOIN(CHAR(10),TRUE,SORT(TRIM(TEXTSPLIT(B5,",")),,,TRUE))

Both approaches achieve the same result.

Legacy Excel

In older versions of Excel that do not offer the TEXTJOIN or TEXTSPLIT functions, you can use a more primitive formula based on the SUBSTITUTE function. If the comma separated text is consistent, you can use a single call to SUBSTITUTE like this:

=SUBSTITUTE(B5,",",CHAR(10))

Here, the source text in cell B5 is provided as the text argument. The old_text argument is a comma without space (",") and new_text is provided by the CHAR function, which returns Excel's line break character:

CHAR(10) // line break

The CHAR function is a way to insert characters via ASCII code numbers. To summarize, SUBSTITUTE replaces all commas with Excel's line break character. This works, but it will leave leading spaces where commas appear with a space (", "). One way to handle this problem is to nest an additional SUBSTITUTE function inside the original formula as the text argument:

=SUBSTITUTE(SUBSTITUTE(B5,", ",","),",",CHAR(10))

Here, the inner SUBSTITUTE is configured to replace all instances of commas with space (", ") with commas without space (","). This effectively normalizes the delimiters before the outer SUBSTITUTE runs. This formula will handle values separated by a single comma followed by a space (", ") and values separated by only a comma (",").

It is important to note that the SUBSTITUTE version of the formula is really just a hack. Unlike TEXTSPLIT, which actually parses the text values in the cell into an array which can be manipulated as needed, SUBSTITUTE has no concept of values separated by delimiters. It simply replaces text values in a text string. As a result, it's not possible to sort the values with this option, like we do with the TEXTSPLIT formula above.

Text-to-columns

Formulas work great when you need a solution that is dynamic, because they will update automatically if data changes. However, if you only need a one-off manual process, you can also use Excel's Text-to-Columns feature to split values into separate cells, then concatenate the values in another formula using CHAR(10) to insert line breaks where needed.