Tutorial - MS Excel - How to Concatenate in Excel
The dictionary meaning of the term concatenate is "link (things) together in a chain or series". In Excel we use the Concatenate formula/function in order to combine the data in multiple columns into a single column.
Text1, Text2,.... = Texts to be concatenated. References to the cells containing the texts
1. Let us take some sample data in an excel sheet and concatenate the data from columns A and B into column C.
2. Click on the first cell in column C to insert the formula for concatenating
3. There are 2 ways to insert formula in the cell
- Select from the 'Formula Menu' in the top header
- Type the formula directly preceded by an "=" symbol
3a. Inserting the formula for concatenate using the formula builder menu:
From the top header menu, go to Formulas > Text > Concatenate
3b. Click on the first cell in Column C, type the "=" sign and start typing the name of the formula i.e., concatenate. A menu of formulas appears and you can select concatenate from the list.
4. When you click on concatenate, the formula is added in the cell and the formula builder appears on the right hand-side of the screen.
5. Click on the first cell in Column A, "A1" appears in the text1 field of the Formula Builder.
Alternatively, you can type "A1" as the first argument in your formula directly:
6. Click on the first cell in Column B, "B1" appears in the text2 field of the Formula Builder.
Alternatively, you can type "B1" as the second argument in your formula directly:
7. Click on the 'Done' button in the formula builder to get the concatenated result in the first cell of column C
Alternatively you can just hit enter after you are done typing the formula directly in the cell to see the concatenated result.
8. To concatenate the remaining cells in the excel sheet, you just need to take the cursor to the bottom right corner of the cell C1, and drag it down till the end of the data so that the formula is copied into all the cells.
9. To concatenate more columns, use the '+' symbol in the formula builder and add more columns to concatenate
10. If you want to add an empty space between the columns in the concatenated data, add an empty space between quotes as Text2 and the data to be concatenated as Text1 and Text3 respectively:
Text1 = A1
Text2 = " "
Text3 = B1
Alternatively, you can type "A1" as the first argument, " " as the second argument and "B1" as the third argument in your formula directly:
This results in a concatenated string in Column C, with a space in between the columnA and columnB data
More MS Excel Tutorials
- Tutorial - MS Excel - How to Substitute or Replace Text with other Text in Excel
This is a step-by-step tutorial to use the 'Substitute' function to replace some text (usually recurring) with other text in an excel sheet.
- Tutorial – MS Excel – How to highlight duplicate values in Microsoft excel without deleting them
Sometimes you may not want to delete the duplicate values but may want to highlight them anyway. Here's a step-by-step tutorial to highlight duplicate values in Microsoft excel without deleting them.
- Tutorial – MS Excel – How to insert a picture in a Microsoft excel worksheet
Step-by-step tutorial to insert a picture in a Microsoft excel worksheet.
- Tutorial – MS Excel – How to Write Text Vertically or at an Angle in an Excel Sheet
This hub describes ways to be able to type text vertically or at an angle in a Microsoft Excel Sheet.
- Tutorial – MS Excel – How to embed a file as an object in a Microsoft excel worksheet
Step-by-step tutorial to embed a file as an object in a Microsoft excel worksheet
- Tutorial – MS Excel – How to Print Comments Attached to a Cell
Ever wondered why the comments attached to cells are not printed when the excel sheet is printed? Step-by-step tutorial on how to print comments attached to cells, while printing a MS excel file.
- Tutorial – MS Excel – How to use VLookup in Microsoft excel
Step-by-step tutorial to use VLookup function to search for a list of values present in a table in Microsoft excel.
- Tutorial – MS Excel – How to freeze columns or rows in an excel sheet
Step-by-step tutorial to freeze columns/rows/panes in Microsoft excel.
© 2017 Petite Hubpages Fanatic