Tip of the Day: Expand Excel Range in Cell Calculation

If you want to get a range of cells (say person names) concatenated together in a single cell, you’ll find that you’re not able to provide the concatenate function with a range.  However, you can tell Excel to expand that cell’s range quite easily.

 

	A	B
1	Bob
2	Sally
3	Joe
4	Steve

 

In the case above, you’d simply put your cursor in cell B1, and type in “=A1:A4”.  Then you’d simply press F9, and that will expand to the following in the cell:

Before pressing F9:

=A1:A4

After Pressing F9:

={“Bob”;”Sally”;”Joe”;”Steve”}

 

Then you can use that list of values and wrap the concatenate function around it.  It may be easiest to copy the contents of the cell into a text editor and do a search/replace on the “semi-colon” character with a comma.

What you ultimately want:

=concatenate(“Bob”,”Sally”,”Joe”,”Steve”)

 

To make it even easier we can add a delimiting (set of) character(s) by adding a few more characters:

(With delimiting characters (comma and space) and) before pressing F9:

=A1:A4&”, “

After Pressing F9:

={“Bob, “;”Sally, “;”Joe, “;”Steve, “}

 

What you’re basically saying above is concatenate the “comma and space” together with each cell in the range when you calculate the cell value.  Pressing F9 essentially forces the cell to calculate the value it will ultimately equate to, and is useful in many other ways than just what’s highlighted above.  But that’s a topic for another day.