Microsoft Excel is useful to concatenate data from separate cells into one cell.
For example, you may have 3 separate columns of first, middle, and last names that need combined:
You can do so in 5 different ways and separate the combined results using delimiters (symbols such as dashes, apostrophes, decimals, etc.), spacing, or line breaks (a new line).
Without adding a separator, your results will be joined such as: “JohnErinDoe”.
For combining multiple cells into a single cell, choose from one of these 5 methods:
- Flashfill *requires a list or pattern for Excel to gauge
- TEXTJOIN function *Requires Microsoft Excel versions 2019 or 365
- Ampersand (&) Symbol
- Concatenate or Concat Operations
- Power Query *Requires a table of the necessary data
Options 2-5 require a separator to avoid the data blending together like “JohnErinDoe”.
To separate use a delimiter (a symbol like a dash, apostrophe, or decimal), a space, or a line break (a new line).
Here’s Several way To Concatenate Cells In Microsoft Excel Using Separators
FLASHFILL:
Flashfill is the easiest option as Excel will automatically fill in the remaining columns.
Just enter a few rows of data and Excel will pick up the pattern.
*Some complex patterns will not be recognized
TEXTJOIN Function:
Only available for Excel versions 2019 and 365!
TEXTJOIN Function is the easiest way to manually combine the data.
Insert the function:
=TEXTJOIN(“Delimiter”, “true/false”, “data 1”, data 2”)
- “Delimiter” is where you insert a symbol to separate your data. In the example above, spacebar is used to add a space between parenthesis, thus adding a space between names.
*Note the spacing did not have to be repeated throughout the formula but simply added in the beginning. - “True/False” – Enter “True” if you would like the result to ignore any empty cells. Enter “False” if you would like any empty cells included in the result.
- “Data 1” and “Data 2” – This is where you may click on your cells to enter the cells to be combined. You may also enter a range of cells like “E4:E8” to combine multiples.
TIP: For users new to functions, I recommend using the formula icon: (fx) next to the formula bar.
(See above example). You can then search for your function, “TEXTJOIN,” in the pop-up box, and the pop-up will lay out the process in a simplified way.
Ampersand (&):
The “&” symbol will connect the cells without using a function.
By placing the “&” symbol between each cell, you will combine them.
For example:
=A1&B1&C1
- Add a separator or delimeter to prevent results from becoming one. “JohnEricDoe”
- In this example, I have used “CHAR(10)” to add a line break into the results.
- Must add every cell and every separator singly. No range for cells.
Use the “Wrap Text” option to show the separations by:
- Clicking the “Wrap Text” icon above your formula bar (see highlighted image above)
OR
- Right-clicking on the designated cell (or column of cells), select “format cells,” select the “Alignment” tab at the top of the pop-up, and click the box (under Text Control) that says: Wrap Text. Your spacing will NOT be visible until you wrap the text!
CONCATENATE OR CONCAT Functions:
Close to TEXTJOIN function, but requires adding cells and delimiters individually
=CONCATENATE(D2,DELIMITER,E2,DELIMITER,F2)
- “John Eric Doe” – The final result when using the CONCATENATE function along with CHARA(10) for line breaks.
- “Mary Jean Smith” – The product of the formula without wrapping the text.
- “Jane Lily Smith” – The product when using spacebar (“”) in place of CHAR(10) to give you an example of using a different delimiter.
- The function button (fx) is highlighted for easy access to formulas.
- “Wrap Text” is highlighted for easy access to the “Wrap Text” step.
CONCAT VS. CONCATENATE – CONCAT function allows a range of cells, whereas CONCATENATE requires cells to be entered individually.
Power Query:
Available if using a table. Select the “From Table/Range” icon.
Power Query Editor will pop up.
Hold “Shift” to select all columns, right-click, and select “Merge Columns”. (Or select “Merge Columns” on the upper toolbar).
In “Merge Columns” pop-up: choose delimiter and/or the name of your new sheet.
After you close the editor, you will find a new sheet at the bottom left-hand corner, clicking this sheet will display your new table.
- To create a table: Select the necessary data, click on the top left “Insert” tab, select the table option on toolbar, select/unselect the box depending on whether your table has premade headers (e.g. First Name, Middle Name, Last Name), then select “Ok”. (Headers may be created if necessary).
All 5 of these methods will combine multiple cells into one, choose which is best for you!
