Explanation

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, but 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 helpful 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 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 + space (”, “) with a comma 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 that 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.

Explanation

At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:

MID(B5,{10,9,8,7,6,5,4,3,2,1},1)

The text argument comes B5, and 1 is specified for the number of characters to extract.

With the string “ABCD” in B5, the output from MID is an array that looks like this:

{"","","","","","","D","C","B","A"}

This array is fed into the TEXTJOIN function as the text1 argument, with delimiter set to an empty string (""), and ignore blank set to TRUE (entered as 1):

=TEXTJOIN("",1,{"","","","","","","D","C","B","A"})

The TEXTJOIN function concatenates each element in the array together, ignoring blanks, and returns the final result, “DCBA”

Dynamic array

The array constant in the above example will only support string up to 10 characters. To use a dynamic array that scales to the right size, you can use a more complicated formula like this

=TEXTJOIN("",1,MID(B5,ABS(ROW(INDIRECT("1:"&LEN(B5)))-(LEN(B5)+1)),1))

More information about generating an array of numbers here .

Dynamic array with SEQUENCE function

Excel 365 supports dynamic array formulas . In Excel 365, the SEQUENCE function can generate dynamic number arrays in one step. With SEQUENCE, the formula above can be simplified to:

=TEXTJOIN("",1,MID(B5,SEQUENCE(LEN(B5),,LEN(B5),-1),1))

Inside SEQUENCE, the LEN function returns the count of characters in B5, 4. This result is used both for the rows argument and the start argument, with -1 provided for the step argument:

SEQUENCE(4,,4,-1) // returns {4;3;2;1}

and this array is delivered to the MID function as the start_num argument.