This week I had a request to show how to join text together from different columns in Excel. I don’t know about anyone else but I personally know that in my early years working in Excel, I spent many many hours retyping data from one column into another when the need arose or when data had been split between so many columns that things were just out of control. Sometimes when a new set of data is created, we don’t envisage what the future requirements of that information will be, so somewhere down the line, the format your data is presented in does not meet your new requirements.
To save yourself some time and some sanity, you can utilise a function in Excel to make this process a breeze. I’d like to introduce the concatenate function. The meaning of the word concatenate is to join things together, and that is exactly what the concatenate function will do for you in Excel. This function is always a popular one during training courses. For users of Excel 2013 and above, you may like to investigate the Flash Fill tool which provides the functionality of concatenate but packages it in an easy to use new feature.
If you are completely new to using formulas or functions then be sure to check out my post on Formula Basics in Excel
To use the concatenate function, follow these steps:
- Open Excel with some data that you wish to join together; alternatively, you can copy the sample data from the examples below. I am going to join together the house number and street name into one cell
- Place your cursor in the next available empty cell in a blank column or insert a new column if needed.
TIP: To insert a new column, place your cursor where you want the new column to be displayed, then from the Home tab click the Insert button and choose Insert Sheet Column
- Enter a heading for the new column and press Enter
- You should now be in the cell which will contain the first set of joined text
- Select the Formulas tab from the Ribbon and choose the Text category, then select Concatenate
- The Function Arguments dialog box will now appear:
- In the Text1 field we need to select the location where the first part of the text can be found, in this case, the house number will be the first part and can be found in cell A2, use your mouse to select cell A2
- So that Excel puts a space between the house number and the name of the street, you need to include a space character. If you selected the cell with the street name in it as text2 without first putting in a space character then Excel will display the two pieces of text with no spaces between.
- Place your cursor in the Text2 field and type a space using the spacebar
- You will now see Text3 and Text4 will be displayed
- Place your cursor in Text3 and then select the location of the next part of text, the street name, or cell B2
- Repeat the previous step and place a space into the Text4 field
- In Text5 this will contain the suburb, so select cell C2
- The Function Arguments dialog box will display a preview of your data:
- Click OK
- The cell will now display the data joined together
- Autofill the formula down the remainder of the column using the AutoFill tool – see my post Introduction to the AutoFill Tool
Important Note: If you delete column A, B or C which contain the individual information, column D will display an error so you must leave them in the spreadsheet. For visual purposes if you wish to have these out of sight, simply highlight the two columns, right mouse click and select Hide.
- Have fun using this function to combine text strings.
If you want to continue and venture into the world of formulas you may like to see the following posts: