Excel Consolidate Text Data: Formulas and Power Query

Formulas & functions · Updated November 19, 2026

To consolidate text in Excel, join cells with TEXTJOIN or combine rows from multiple tables with Power Query. With text parts in A2 and B2, enter =TEXTJOIN(" ",TRUE,A2,B2) to place a space between them and ignore empty cells; for stacking whole datasets, use Power Query’s Append feature instead.

What “consolidate text” means in Excel

People use “consolidate” to mean a few different jobs. You might want to join a first and last name into one cell, assemble an address from several fields, gather a list of labels into one sentence, or bring records from several worksheets into one table. The right tool depends on whether your desired result is one combined text value or a larger dataset made from many rows.

For a single combined value, formulas such as TEXTJOIN, the ampersand operator (&), or CONCAT are usually suitable. For combining tables vertically while keeping each record in its own row, Power Query’s Append feature is generally more manageable. Excel’s Data > Consolidate command is a different tool: it summarizes corresponding numeric values and does not join text strings together.

Keeping that distinction in mind prevents a common frustration: selecting Data > Consolidate and expecting Excel to concatenate names or descriptions. It is built for calculations such as totals and averages, not for creating a sentence from text.

Join text from cells with TEXTJOIN

TEXTJOIN is useful when you need to combine text values with a chosen separator. Its arguments specify the delimiter, whether to ignore empty cells, and the cells or ranges to combine. For example, with a first name in A2 and a last name in B2, this formula returns “Mina Patel”:

With the first name in A2 and the last name in B2, enter =TEXTJOIN(" ",TRUE,A2,B2).

The first argument is a space, so Excel inserts one between the values. TRUE tells Excel to ignore empty cells. That matters in a list of optional fields: if B2 is blank, the result is “Mina” rather than a name with an extra separator at the end. Change the delimiter to a comma and space, semicolon, line break, or another character to suit the output.

For example, with product labels in A2:A5 containing “Desk”, “Lamp”, a blank, and “Shelf”, use =TEXTJOIN(", ",TRUE,A2:A5). The result is “Desk, Lamp, Shelf”. The blank is omitted, and the delimiter appears only between included values. TEXTJOIN is available in current Microsoft 365 Excel and Excel 2019 or later.

Combine fields down a worksheet

When every row needs its own combined result, enter a formula in the first result row and fill it down. Suppose column A contains first names and column B contains surnames, beginning in row 2. Put the formula below in C2, then copy or fill it down the rows that contain data:

With first names in A2:A100 and surnames in B2:B100, enter =TEXTJOIN(" ",TRUE,A2,B2) in C2 and fill down. For the row where A2 is “Mina” and B2 is “Patel”, C2 returns “Mina Patel”.

On Windows, you can drag the fill handle at the bottom-right corner of C2, or select the target range and use Ctrl+D to fill down from its top cell. On Mac, the equivalent shortcut is Command+D. Check that the references adjust as intended: the formula in C3 should refer to A3 and B3. If the formula will be copied into other locations but must always refer to a particular cell, use dollar signs to make that reference absolute.

For a simple two-cell join, =A2&" "&B2 is another valid option. It is concise, but it does not have an ignore-empty setting; a blank field can leave the separator in an awkward position. TEXTJOIN is often clearer when some source cells may be blank or when you need to combine a range.

Combine text from multiple worksheets

If you only need a text list assembled from a few known worksheets, a formula can refer to each sheet’s range. For instance, suppose North and South each have a list of department names in A2:A4, and you want one comma-separated list. With those ranges in place, enter:

=TEXTJOIN(", ",TRUE,North!A2:A4,South!A2:A4)

If North contains “Sales” and “Support”, while South contains “Design” and “Finance”, the result is “Sales, Support, Design, Finance”. The referenced ranges can be separate arguments to TEXTJOIN. If a worksheet name contains a space or punctuation, surround it with single quotes in the reference, as in 'East Region'!A2:A4.

This method makes a single text value; it does not build a clean, refreshable table of records. It can also become cumbersome when there are many sheets or when their sizes change. If the goal is to stack complete records from several worksheets, use Power Query rather than turning all the records into one long cell.

Append tables with Power Query

Power Query is designed to import, reshape, and combine tabular data. Its Append operation stacks rows from one query beneath rows from another, matching columns by their header names. This is a good fit for monthly reports, regional lists, or exports that share a consistent set of fields. Text values remain in separate cells and rows, so the result is still usable for sorting, filtering, and analysis.

  1. Format each source range as an Excel table, ideally with the same column headers. Select a range and choose Insert > Table, or use Home > Format as Table.
  2. Select a cell in a source table and choose Data > From Table/Range to open it in Power Query. Confirm the headers are recognized correctly.
  3. In the Power Query Editor, choose Home > Append Queries > Append Queries as New. Select Two tables or Three or more tables, as appropriate, and choose the queries to append.
  4. Check the result, including column names and data types. Then choose Home > Close & Load to return the combined result to Excel.

Power Query lines up fields using column names, not simply their left-to-right positions. If one source calls a field “Customer” and another calls it “Client”, those may appear as separate columns rather than being treated as the same field. Standardize headers before appending when the fields represent the same information. Columns that exist in only one source can still be included; records from sources without that field will have empty values in that column.

The query result is not a live formula that changes immediately whenever a source cell changes. Refresh the query to bring in updated source data; depending on the workbook setup, use Data > Refresh All. In Microsoft 365 for Windows, the Power Query workflow is available through the Data tab. Power Query is also available in Microsoft 365 for Mac, but connector support and editor features can vary by build. In Excel for the web, capabilities for creating and editing queries are more limited than in the desktop app, so use desktop Excel when the full authoring workflow is needed.

Why Data Consolidate does not join text

Excel’s Data > Consolidate command is intended to combine values from multiple ranges using a summary function. On Windows, find it on the Data tab in the Data Tools group. Functions include calculations such as Sum, Average, Count, Max, and Min. You can consolidate by matching cell positions or, when source ranges have labels, by matching row or column labels.

That feature is useful when, for example, separate sheets contain numeric sales figures arranged in the same layout and you want their totals. It is not a text concatenation tool: it will not turn values such as “North” and “South” into “North, South”. For text lists, choose TEXTJOIN if the output belongs in one cell, or Power Query Append if the output should remain a table. The command’s purpose is easy to confuse with the everyday meaning of “consolidate,” but the distinction is important.

Common problems and how to avoid them

  • Unexpected spaces or punctuation: Choose the delimiter deliberately. A space is right for names; a comma and space may suit a list. With TEXTJOIN and ignore_empty set to TRUE, empty cells do not produce repeated delimiters.
  • A value looks blank but still appears: A cell containing a space character is not empty. Clear the cell or clean the source data if it should be omitted. Also check whether cells contain formulas returning an empty string or invisible characters from an import.
  • Text is cut off: Excel limits a cell’s contents to 32,767 characters. A very large joined list can exceed that limit. Keep large datasets in rows with Power Query rather than forcing them into one cell.
  • Power Query creates duplicate-looking columns: Compare source headers for spelling, spacing, and punctuation. Normalize the column names before appending so equivalent fields line up.
  • New source rows are missing: Confirm the query’s source is an Excel table that expands as rows are added, then refresh the query. A fixed range may not include rows entered beyond its original boundary.
  • Numbers or dates look different after combining: Check data types and formatting. A number stored as text is not identical to a numeric value, and appended data can expose inconsistencies between sources. Standardize values and types before relying on the result.

Choosing the right approach

Start with the shape of the result you want, not with the word “consolidate.” Use a formula when you are joining a few fields into a display value or making a concise list in one cell. Use Power Query when multiple tables need to become one structured table that can be refreshed and analyzed. Use Data > Consolidate when the job is to calculate a summary across similarly arranged ranges, especially numeric data.

Consider how often the source changes, too. A worksheet formula updates when its referenced cells recalculate, while a Power Query output generally needs a refresh. For a one-off report with a handful of values, a formula may be quicker. For a recurring monthly process with many rows, a query can reduce repetitive copying and help preserve a consistent sequence of cleaning and combining steps.

Finally, decide whether the combined result is meant for people to read or for later analysis. A comma-separated cell is convenient for a label, email, or brief description, but it is less useful for filtering individual items. If each item should remain independently sortable, keep items in separate rows or columns instead of joining them into one text string.

Frequently Asked Questions

Can Excel combine text from multiple cells into one cell?
Yes. Use TEXTJOIN with the delimiter you want and set its ignore-empty argument to TRUE if blank cells should be skipped. For two cells, the ampersand operator is another option.

How do I combine text from several sheets?
For a short, known list of ranges, refer to each worksheet range as an argument in TEXTJOIN. To combine whole tables from many sheets while keeping records in rows, append their queries in Power Query.

Can Data > Consolidate merge text?
No. Data > Consolidate summarizes ranges with functions such as Sum or Average; it does not concatenate text. Use a text formula or Power Query, depending on the result you need.

How do I combine first and last names without an extra space?
Use TEXTJOIN with a space delimiter and TRUE for the ignore-empty argument. This skips a blank first or last name rather than leaving the separator where that value would have been.

Does Power Query update the combined table automatically?
Not as an immediate cell-by-cell formula does. Refresh the query to retrieve changes from its sources. An Excel table can help the query include newly added rows, provided the query is based on that table.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.