How to Use Consolidate Function for Text Data in Excel?

While working on multiple worksheets, you may want to consolidate text data with the Excel function. So you can combine text data from multiple worksheets into a single worksheet. You can also consolidate text data from multiple columns, with or without any conditions. In this article, I will show you how you can use consolidate function for text data in Excel.

How to Use Consolidate Function for Text Data in Excel: 3 Suitable Examples

I will you 3 different examples to consolidate text data in Excel. One example will contain consolidating text data from multiple worksheets, and the other two will combine text data from columns with or without any conditions.

1. Perform Consolidate Function for Text Data from Multiple Worksheets with Power Query Tool

Suppose, you have a dataset of products that have passed the sales target of a month or not in multiple worksheets. So, now you want them to consolidate text data in one worksheet. Follow the steps below to consolidate or combine data from multiple sheets with data using Power Query in Excel.

• First, check whether all the worksheets are of the same row and column headers or not.

• Now, go to the Data tab > From Table/Range option.

• As a result, the “Create Table” window will appear.
• Then, select the data table of the first worksheet.
• Mark the checkbox saying “My table has headers”.
• Finally, press OK.

• So, now the power query window will appear.
• You can rename the table name from the name box.
• Now, click on the “Close & Load” and select the “Close & Load to” option.

• Then, select the “Only Create Connection” option in the “Import Data” window.
• Then press OK.

• Then, do similar things for the remaining worksheet data and create tables in the power query.
• Now, go to the Data tab and press the Queries & Connections option.
• There will be a window on the right side of the worksheet.

• Now, right-click on Table 1 and select the Append option.

• Now, the Append window will appear.
• Select three or more tables in the options.
• Then, add Table 2 and Table 3 to the Tables to Append list.

• So, you will see the text data of the same pattern are consolidated under the same header.

• Now, import the data to the worksheet from the power query.
• For this, click on the Close & Load option from the top ribbon.

• As a result, you will see a new worksheet will be created with consolidated text data from the worksheets.

2. Perform Consolidate Function for Text with TEXTJOIN

Now, if you want to consolidate data in Excel from multiple columns without any conditions then you can use the TEXTJOIN function, follow the steps below:

• Create a column to get the consolidated text data from the columns.
• Then paste this formula into the cell G5:

• Then use the Fill Handle icon to drag the formula to the other cells, or use shortcuts Ctrl+C and Ctrl+P to copy and paste.

• As a result, you have to get the consolidated text data with commas as delimiters.

3. Consolidate Text with a Condition Using TEXTJOIN-FILTER Combination

In addition, you may need to consolidate text data from columns with conditions. Suppose you want to get a list of products in a cell that have passed the sales target and which have failed in another cell. Then you have to use the TEXTJOIN function with the FILTER function. Follow the steps below:

• First, make columns to create consolidated text data for the conditions.
• So, make conditions Pass and Fail to get the list of products in cells.

• Then, paste this formula into the cell E5.

• So, it will give the lists of products of range B5:B15 which satisfies the filter criteria = “Pass” for the cells in range C5:C15 with commas in between them as delimiters.

• Then, drag the formula to get the list of products.

Things to Remember

• You can’t use the Consolidate feature of Excel to combine text data. It can consolidate only number data.
• You should use the power query feature to consolidate text data from multiple worksheets.
• You can use TEXTJOIN, CONCATENATE, and some more functions to consolidate text from multiple columns in one column.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

In this article, you have found how to consolidate functions in Excel for text. After reading this article, I believe, you will be able to consolidate text data from multiple worksheets using Power Query and combine text from multiple columns using the TEXTJOIN function. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel