Sometimes you need to take text or numbers from several cells and combine it all into one cell. A classic example of this is when you have a column for first name and a column for last name, but you need to show names in “last name, first name” format. That is a job for Concatenate.
If you have first names in column A of your spreadsheet, and last names in column B, here is how you can combine them in column C. Start with your cursor in cell C1. Select the Formulas ribbon, click the Text button, and choose Concatenate. In the Text1 field, type B1, or click on cell B1 to add the last name. Since we don’t want the last and first name strung together wthout spaces, enter a comma followed by a space in Text2. At Text3, enter A1, or click on that cell with your mouse. At the bottom of the formula window you will see what the result will be of the formula you are building. Click OK to record the formula. Now in cell C1 you will see the last name and the first name, separated by a comma.
For a simple instance like this, you may want to try this shortcut. You can use the & character in formulas to join text and number just like you can with Concatenate. In our example you would type =B1&”, “&A1 in cell C1.
Here is another example that helps show how flexible Concatenate can be. You are not limited to cell references or text for the entries in each Text box, you can use other formulas ans functions within the concatenate formula. If you wanted to combine the first 4 letters of the last name with the fist 5 letters of the first first name, to make a customer ID, you could do that by combing the Left function with Concatenate. You will start just like you did in the first example, but at Text1, enter LEFT(B1,4). And for Text2, enter LEFT(A1,5). That will combine the first 4 characters (starting from the left) from cell B1 with the first 5 characters from cell A1.
