To consolidate text from Excel cells, use TEXTJOIN when you want a separator and control over blank cells, or join values directly with & or CONCAT. For example, with text in A2:C2, =TEXTJOIN(" ",TRUE,A2:C2) joins the entries with spaces and skips empty cells.
What “consolidate text” can mean in Excel
People use “consolidate” to mean several different things: combining words from cells into one text string, bringing records together from multiple worksheets, or summarizing data from several ranges. These tasks call for different tools. If the goal is one readable string—such as a full name, address, or list of tags—use a text formula or Flash Fill. If the goal is to combine rows from tables, Power Query is usually more appropriate. Excel’s Data Consolidate command is primarily for summarizing numeric data, not for stitching text together.
It helps to decide what the finished result should look like before choosing a method. A full name might need a space between parts; a list of categories might need commas; a combined address may need line breaks. Also decide what should happen when one of the source cells is empty. TEXTJOIN can omit empty cells, while other methods may leave extra separators or spaces that need cleanup.
Use TEXTJOIN for a separator and blank handling
TEXTJOIN is usually the most flexible formula for combining text. Its arguments specify the delimiter, whether empty cells should be ignored, and the values or ranges to join. In Microsoft 365 for Windows, enter the formula in the destination cell and press Enter. The result updates when the source cells change.
With A2:C2 containing North, Region, and 7, respectively, enter =TEXTJOIN(" ",TRUE,A2:C2) to return North Region 7. The first argument is a space, and TRUE tells Excel to ignore empty cells. Change the delimiter to a comma and a space when creating a readable list.
With Red in A2, a blank cell in A3, and Blue in A4, enter =TEXTJOIN(", ",TRUE,A2:A4) to return Red, Blue. Because the empty cell is ignored, the result does not begin or end with a comma and does not contain a doubled separator. If you instead use FALSE for the ignore-empty argument, Excel includes empty positions in the join; the delimiters can therefore appear next to one another.
TEXTJOIN works with a range as well as separate cell references, which makes it convenient for joining a row or column. It is available in Microsoft 365 and Excel 2019 or later. One limit to keep in mind is Excel’s maximum cell text length: a result longer than 32,767 characters returns a value error.
Choose CONCAT or the ampersand for simple combinations
For a short combination, the ampersand operator is easy to read. With a first name in A2 and a last name in B2, enter =A2&" "&B2 to return the two names with one space between them. The ampersand joins each part in sequence, and the quoted space supplies the separator. If B2 is blank, this formula still leaves the trailing space after the first name.
CONCAT is useful when you want to join several cells or ranges without adding a delimiter. With text in A2:C2, enter =CONCAT(A2:C2) to join their contents in order, with no spaces or punctuation inserted. If the cells contain North, Region, and 7, the result is NorthRegion7. Add a delimiter yourself with TEXTJOIN or use ampersands when the spacing matters. CONCAT is available in Microsoft 365 and Excel 2019 or later.
For a two-part value, ampersands often make the intended spacing obvious. For a longer list where blank cells are possible, TEXTJOIN is less prone to stray separators. Older workbooks may contain the CONCATENATE function; it remains supported for compatibility, but Microsoft recommends CONCAT or the ampersand operator for new formulas.
Combine values across rows or worksheets
To make one combined result for each record, place a formula in the first result row, then fill it down. For example, with a city in A2 and a postal code in B2, enter =TEXTJOIN(" - ",TRUE,A2:B2) to return a value such as Lakeview – 12345. Copy the formula down using the fill handle, or select the destination range and use Ctrl+D on Windows to fill down. On Mac, the fill-down shortcut is Control+D. If new records will be added regularly, converting the source range to an Excel table with Ctrl+T on Windows can make formulas easier to extend; on Mac, use Command+T.
You can also join corresponding cells from different worksheets. With the relevant entries in B2 on sheets named Jan, Feb, and Mar, enter =TEXTJOIN("; ",TRUE,Jan!B2,Feb!B2,Mar!B2) to combine those three entries, separated by semicolons and spaces. This formula combines only those specified cells; it does not automatically gather every row from each sheet. For a large or growing set of monthly tables, use Power Query to append the records into one table rather than writing a long formula for every position.
Use line breaks or other separators
A separator can be any text you put in quotation marks, not just a comma or space. A slash, semicolon, bullet character, or label can work when it makes the output easier to interpret. Choose a delimiter that will not be confused with characters already present in the source text. For example, a comma may be unsuitable if the source values themselves contain commas.
For a multiline address or note, use CHAR(10) as the delimiter. With address components in A2:A4, enter =TEXTJOIN(CHAR(10),TRUE,A2:A4). The result places each nonblank component on a separate line within the same cell. Select the result cell and choose Home > Wrap Text so the lines display within the cell. You may also need to adjust the row height. The line break is part of the cell’s text, so it can be copied or used by other formulas.
TEXTJOIN does not automatically clean spaces already stored in the source cells. If a source value has leading or trailing spaces, those remain next to the delimiter and can make the result look uneven. Correct the source data, or use a cleanup step such as TRIM on the source cells before joining. Check the outcome with values that include blanks and irregular spacing, not just with a perfect sample row.
When Flash Fill is a better choice
Flash Fill is useful when you want Excel to recognize a pattern from examples and create a one-time set of combined results. Suppose first and last names are in columns A and B. Type the desired full name in the first result cell, type another example in the next row, then choose Data > Flash Fill. On Windows, Ctrl+E is the Flash Fill shortcut. Excel infers the pattern and fills the remaining rows when it can identify it clearly.
Unlike a formula, Flash Fill creates values rather than a live calculation linked to the source cells. If a name changes later, the filled result will not automatically update. Review the results, especially when the data has inconsistent formats, middle names, or blank entries. Flash Fill is a good choice for a quick transformation that does not need to stay connected to its inputs; use a formula when the output should recalculate.
Use Power Query for repeatable data consolidation
Power Query is designed for importing and shaping data, so it can be a better fit when consolidation involves many rows, multiple files, or repeated monthly updates. For a table in a worksheet, select a cell in the table and choose Data > From Table/Range. In the Power Query Editor, select the text columns to combine, then choose Transform > Merge Columns. Choose a separator, provide a new column name, and confirm. When the result is ready, choose Home > Close & Load to return it to Excel.
Merging columns in Power Query combines fields across each row, such as first name and last name. Appending queries stacks rows from tables with matching or compatible columns. These are different operations: merge columns makes a combined field, while append brings records together vertically. Power Query keeps a sequence of transformation steps that can be refreshed when source data changes, although you should confirm that the source files and table structures remain available and consistent.
Power Query menus can vary slightly depending on the Microsoft 365 update and whether the source is a table, file, or database. On Excel for Mac, Power Query availability and supported data sources depend on the version; the Windows ribbon path and feature set should not be assumed to match every Mac installation. If Merge Columns is unavailable in your version, a worksheet formula may be the simpler option.
Why Excel’s Consolidate command is different
The command named Consolidate is not a general-purpose text-combining feature. On Windows, find it under Data > Data Tools > Consolidate. It is intended to summarize values from multiple ranges, commonly by sum, average, count, or another listed calculation, using matching positions or labels. It does not concatenate text labels into a sentence or list.
If you need to bring together rows of text records, consider Power Query’s append operation. If you need one text result from several cells, use TEXTJOIN, CONCAT, or ampersands. This distinction prevents a common dead end: choosing the Consolidate command because its name sounds right, then finding that it does not produce a joined text string.
Common problems and practical checks
- Unexpected extra spaces: Check for spaces at the start or end of source values. The delimiter adds its own characters, but it does not remove existing whitespace.
- Missing text: Confirm that the cells you expect to combine are within the referenced range and that they contain text or values rather than formulas returning an empty string.
- Repeated delimiters: In TEXTJOIN, use TRUE when empty cells should be skipped. If a cell contains a space rather than being truly empty, Excel treats it as content.
- Old results after editing: Formula results recalculate, but Flash Fill outputs are static values. Check whether the chosen method matches the need for automatic updates.
- Formula shown instead of result: Check that the cell is not formatted as Text, and confirm that the formula begins with an equals sign. If the cell was already text-formatted, change its format to General and re-enter the formula.
- Result too long: A single Excel cell cannot hold more than 32,767 characters. Split the output across cells or retain the source values separately if the combined text exceeds that limit.
Frequently Asked Questions
How do I combine text from two cells in Excel?
With the values in A2 and B2, use =A2&" "&B2 to put a space between them. Use TEXTJOIN instead if you want to control how blank cells are handled.
How do I combine cells and ignore blanks?
With the values in A2:A10, enter =TEXTJOIN(", ",TRUE,A2:A10). The formula returns nonempty entries separated by comma-space, without adding separators for blank cells.
Can Excel combine text from multiple worksheets?
Yes. Reference each required cell in a formula, or use Power Query when combining complete tables from several sheets. A formula joins only the cells or ranges you specify; it does not discover all worksheets automatically.
Does Data Consolidate join text?
No. Excel’s Consolidate command summarizes values across ranges; it is not designed to concatenate text. Use TEXTJOIN or CONCAT for a text string, and Power Query append when the task is stacking records.
Will a combined formula update when the source changes?
Yes, a formula such as TEXTJOIN recalculates when its referenced cells change, subject to Excel’s calculation settings. Flash Fill produces static values, so those results do not update automatically when their source entries are edited.
Related
- Excel Data Validation: Lists, Rules, and Setup
- Remove Data Validation Restrictions in Excel
- Excel Data Validation: Rules, Lists, and Input Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIFS: Sum Values That Meet Multiple CriteriaTo sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in…
- Excel Compare Two Sheets for Matching Names: FormulasTo compare names in two worksheets, enter a formula beside the names on the first sheet and check…
- Excel Column Subtraction Formula: Examples and TipsTo subtract values in one Excel column from the matching values in another, enter =A2-B2 in the first…
- Excel Extend Number Sequence: Fill Series and FormulasTo extend a number sequence in Excel, enter its first two values, select both cells, then drag the…
- XLOOKUP Between Two Sheets in Excel?Yes. To look up a value on one Excel sheet and return a related value from another, use…
- Advanced Excel HLOOKUP Formula: Examples and TipsFor an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.