Did you ever wonder if there was an Excel formula that could split a text into columns or rows? Well, there is now!
Introducing Split Text XL, which provides the SPLITTEXT formula. Use the SPLITTEXT formula to split text to columns or rows by a specified delimiter. The formula works similar to the “Text to Columns” feature in Excel, but also offers the option to split text to rows.
Use Split Text XL to split full names into first and last names, or use it to format CSV data as needed.
This article discusses options when trying to join together cell values in Excel.
Many times in Excel you may want to combine several cells into one cell. For example, if your sheet has dates scattered across different cells, you may want to bring them all together into one comma-delimited list of dates. Excel has several formulas that can do this, and here they are:
Excel Formula
How Good Is It?
CONCAT
TEXTJOIN
CONCATENATERANGE
Let’s discuss each of these formulas one by one. The CONCAT formula replaces the old CONCATENATE formula. CONCAT improves upon CONCATENATE because you can use it to join a range of cells rather than just cells. For example, CONCAT(A1:A3) brings together all cells between A1 and A3. However, one drawback of CONCAT is that there is no way to specify a delimiter, such as a comma (,), to separate each value within the result. Another drawback of CONCAT is that the values will not be formatted, which can be critical if you are working with dates, for example.
The TEXTJOIN formula is better than CONCAT, because you have the option to specify a delimiter. So, TEXTJOIN(“,”, A1:A3) results in a comma-delimited list of the values drawn from A1 thru A3. There is a problem with TEXTJOIN however – the cells being joined will not be formatted in the result.
That brings us to the ultimate solution – the CONCATENATERANGE formula. The CONCATENATERANGE formula does everything the TEXTJOIN and CONCAT formulas do, in addition to formatting the result using the individual cell formats within the range. The other big plus of CONCATENATERANGE is that it works with ALL versions of Excel.
Tumbling Wave Software 