Two common tasks frequently encountered in Excel include splitting content in a cell to multiple rows or to combine content from multiple rows into a cell.

It turns out that Excel has a tool that can do “Almost” that by a click of a button. No macro, no formulas. The method here does not apply to Google Sheet.

Here are two demonstrations:

Splitting list in column A to produce column C

Combining column E to give cell F1

Note that this effect does not affect other columns, and it has the downside of not being able to strip out delimiting characters, such as comma in the above example.

Workbook download: Demo and Exercise

Splitting Into Rows

Select the cells in column A.Go to Home tab, select Fill dropdown in Editing section, click Justify.

If a warning message comes up, click OK.

That is it.

For other lists, it is safer to adjust column width narrower, to say one character width, then use Justify.

The Justify function tries to fit content to column width, taking into account spaces (but not line breaks) between words. It does not matter the length of each word.

Combining Rows Into One or More Cells

This operation is an art in itself.

One Cell

The simplest form is to combine all text into one cell only.

To do that, estimate or calculate the number of characters in the rows then allow a wider column width.

To calculate the number of characters, use

=SUM(LEN(A1:A10))

where A1:A10 is the list of cells to combine.

In this case, the result is 61, and so column width should be several points wider at the very least.

In this case the column width need to be widened. You can see the width showing up as you drag the column edge.

This is a rough estimate only, as the precise column width to fit these characters will depend on the font used as well. Also, Excel inserts a space between words if there were none beforehand.

Multiple Cells

Suppose the aim is to group the terms in pairs, it would require some trial and error.

At the very least, you can adjust to a “good enough” result where a word may be in the wrong place, then simply do a copy-paste if the list is not long.

The rule of thumb is to look for the two words that will give you the shortest pair, then use that as a rough guidance as to what column width to set.

Doing It the Easy Way

The above shows a creative use of Justify function that does not solve the problem in a clean way.

To split cells into rows, you would normally want to change the row positions of adjacent cells, as well as to remove the delimiting character once you are done:

That is why you will need a text manipulation tool that will work as your Excel Swiss Knife.

Purchase a macro that will do just that, plus

  1. Add alternating colours swiftly
  2. Insert check boxes, and control the text next to them!
  3. Fill blanks in column
  4. and much more

 

 

 

 

 

 

 

Pin It on Pinterest

Share This