concatenate rows in excel to include a line break

#Concatenate rows in excel to include a line break how to#
#Concatenate rows in excel to include a line break free#

This is handy if you want to add commas, spaces, dashes, or any character between the joined cells. Separator Character – You can also add a separator character between each cell.Formula Type – The Concatenate Macro gives you the option of creating a Concatenate or Ampersand formula.The Concatenate or Ampersand formula is then created in the active cell.The macro splits the range into single cell references, so this range reference (A2:C2) is turned into (A2,B2,C2).You can select a range of cells with the mouse or keyboard. An InputBox appears and prompts you to select the cells you want to concatenate.Select the cell where you want the formula to be input and run the macro.The macro basically splits the range reference that is specified with the InputBox, and then creates the formula in the active cell. The macro makes it really fast to create the formulas. You can assign the macro to a button in the ribbon or keyboard shortcut. It then creates the Concatenate or Ampersand formula by creating an argument for each cell in the selected range. The Concatenate Macro uses an InputBox that allows you to select a range of cells. The following screencast shows the macro in action. So I wrote a macro that makes it possible to concatenate a range. Unfortunately there is no simple way to select the entire range you want to concatenate. It is a handy shortcut if you are concatenating a few cells, but it can still be time consuming if you are joining a lot of cells together. This is probably the fastest way to add multiple cells to your concatenate formula. You do NOT need the macro for this, it is built into Excel.

#Concatenate rows in excel to include a line break how to#

The following screencast shows how to use the Ctrl+Left-click shortcut. This saves time over having to type a comma after each cell selection. You can hold down the Ctrl key while selecting cells to add to the CONCATENATE formula. Option #1: Ctrl+Left-click to Select Multiple Cells This makes it difficult and time consuming to write the formulas if you have a lot of cells to join together (concatenate). However, you cannot concatenate a range of cells by referencing the range in the CONCATENATE function. One popular use is for creating VLOOKUP formulas based on multiple criteria. The CONCATENATE function can be very useful for combining values of multiple cells into one cell or formula.

Skill level: Intermediate Concatenate: The Good & Bad This includes the Ctrl+left-click method, and a free VBA Macro that makes it quick & easy to create the concatenate or ampersand formulas.

#Concatenate rows in excel to include a line break free#

This includes the Ctrl+left-click method, and a free VBA Macro that makes it quick & easy to create the concatenate or ampersand formulas.īottom line: Learn two different ways to quickly (join) concatenate a range of cells. Bottom line: Learn two different ways to quickly concatenate (join) a range of cells.