How to consolidate data in excel from multiple columns
You will start just like you did in the first example, but at Text1, enter LEFT(B1,4). 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 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. Here is another example that helps show how flexible Concatenate can be. In our example you would type =B1&”, “&A1 in cell C1.
You can use the & character in formulas to join text and number just like you can with Concatenate. Now in cell C1 you will see the last name and the first name, separated by a comma.įor a simple instance like this, you may want to try this shortcut. At the bottom of the formula window you will see what the result will be of the formula you are building. At Text3, enter A1, or click on that cell with your mouse. Choose to consolidate by label or by position. Since we don’t want the last and first name strung together wthout spaces, enter a comma followed by a space in Text2. To consolidate multiple Excel files into one, do the following: Click Consolidate Sheets on the Ablebits Data tab. In the Text1 field, type B1, or click on cell B1 to add the last name.
Select the Formulas ribbon, click the Text button, and choose 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. 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. Sometimes you need to take text or numbers from several cells and combine it all into one cell.