Note that a space sometimes appears with the comma, but it is not consistent.
This means we also need to handle this variation.
TheCHAR functionreturns a character when given a validASCII code page number.
TEXTJOIN then joins the values in the array with line breaks and returns the result to cell D5.
you’re able to enable Text wrap on the home tab of the ribbon in the Alignment group.
Or, you could display Format cells with the shortcutControl + 1then enable text wrap on the Alignment tab.
This is because TEXTSPLIT returns anarray of values.
To summarize, SUBSTITUTE replaces all commas with Excel’s line break character.
This effectively normalizes the delimiters before the outer SUBSTITUTE runs.
It is important to note that the SUBSTITUTE version of the formula is really just a hack.
It simply replaces text values in a text string.
TEXTSPLIT can split text into rows or columns.
TEXTJOIN Function
The Excel TEXTJOIN functionconcatenatesmultiple values together with or without a delimiter.
CHAR can insert characters that are hard to enter into a formula.
