Hi - Dave here.
Happy Friday!
This week we look at how to replace commas with line breaks using a formula. More generally, the problem is how to replace one delimiter with another.
There are two basic approaches to this challenge. The first and best approach is to use the TEXTSPLIT and TEXTJOIN functions as seen in the workbook below. Another approach is to find and replace commas with line breaks using the SUBSTITUTE function. Both methods are explained in the article.
[
Download the workbook and read the full explanation]
The big advantage of using TEXTSPLIT is that the result from TEXTSPLIT is an
array. This means you can easily remove extra space with the TRIM function, and even sort the values with the SORT function before they are recombined with TEXTJOIN. Click the link above to read the explanation and download the workbook.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
