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

Get FREE Advanced Excel Exercises with Solutions!

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.

Consolidate Function for Text Data in Excel

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

Consolidate Function for Text Data in Excel

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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

Consolidate Text Data from Multiple Worksheets Using Power Query

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


Similar Readings


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:
=TEXTJOIN( " , ",TRUE,C5:F5)
  • 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.

Using the TEXTJOIN Function

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

Using the TEXTJOIN Function

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.

with a Condition Using TEXTJOIN Function

  • Then, paste this formula into the cell E5
=TEXTJOIN(", ",TRUE,FILTER($B$5:$B$15,$C$5:$C$15=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.

with a Condition Using TEXTJOIN Function

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

with a Condition Using TEXTJOIN Function

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

Osman Goni Ridwan
Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo