Consolidate Function for Text Data in Excel (with 3 Examples)

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. And you can also consolidate text data from multiple columns with or without any conditions. In this article, I will show you how you can consolidate function for text data in Excel.

Download Practice Workbook

You can download the practice workbook from here:

3 Examples to Perform Consolidate Function for Text Data in Excel

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 consolidated in one worksheet. Follow these steps below to consolidate worksheets with text 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 the similar things for the remaining worksheets data and create tables in the power query.
• Now, go to the Data tab and press the Queries & Connections option.
• There will create 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 create with consolidated text data from the worksheets.

Read More: How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)

Similar Readings

• How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
• Use Grouping and Consolidation Tools in Excel (5 Easy Examples)
• How to Remove Consolidation in Excel (2 Handy Methods)
• [Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)
• How to Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)

2. Perform Consolidate Function for Text with TEXTJOIN

Now, if you want to consolidate text data from different 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.

Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)

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 has 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.

Read More: How to Automate Consolidation in Excel (with Easy Steps)

Things to Remember

• You can’t use 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.
• And you can use TEXTJOIN, CONCATENATE, and some more functions to consolidate text from multiple columns in one column.

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 TEXTJOIN functions. I hope you found this article helpful. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Consolidate Two Sheets into One in Excel (3 Useful Methods)
• How to Consolidate Data by Category in Excel (2 Suitable Examples)
• Consolidate Multiple Worksheets into One PivotTable (2 Methods)
• How to Consolidate Data from Multiple Workbooks in a Single Worksheet
• Create a Linked Consolidation in Excel (2 Useful Methods)
• How to Build a Static Consolidation in Excel (2 Suitable Examples)
• Data Validation and Consolidation in Excel (2 Examples)