Yes—if by “blank wells” you mean blank cells between values, Excel can combine the nonblank entries into one cell with TEXTJOIN. With values in A2:A6, use =TEXTJOIN(", ",TRUE,A2:A6); if A2 is Apple, A3 is empty, A4 is Pear, A5 is empty, and A6 is Plum, the result is Apple, Pear, Plum. Truly blank cells contain nothing to combine, so the useful task is usually joining the entries around them while skipping the blanks.
What “consolidate” can mean in Excel
People use “consolidate” to describe two different spreadsheet jobs. One is combining text from several cells into a single cell, perhaps turning a list of names or labels into a comma-separated line. The other is summarizing values from multiple ranges, such as adding monthly totals. Excel handles those jobs differently.
For joining text while ignoring empty cells, TEXTJOIN is the direct option in Microsoft 365 for Windows. Its arguments specify a separator, whether empty entries should be ignored, and the text or ranges to join. It does not merge cells or change the source values; it creates a result in the cell where you enter the formula.
Excel’s ribbon command named Consolidate is for summarizing numeric data from one or more ranges. It can calculate results such as Sum, Average, Count, Max, or Min, but it does not concatenate words into a sentence. Choosing the right meaning first prevents a common mistake: using a numeric summary tool when the goal is to join text.
Join a range and skip empty cells
Suppose A2:A6 contains the example values Apple, blank, Pear, blank, and Plum. With those entries in A2:A6, the formula =TEXTJOIN(", ",TRUE,A2:A6) returns Apple, Pear, Plum. The comma followed by a space is the delimiter placed between included entries. The TRUE argument tells Excel to ignore empty cells, so it does not add extra separators for A3 or A5.
Enter the formula in a cell outside the source range—for example, B2—and press Enter. If the source values change, the result updates automatically. You can replace the comma-and-space delimiter with a semicolon, a space, a slash, or another text separator. To join values without any separator, use an empty text delimiter.
For a horizontal list, the same function works across a row. With labels in A2:F2 and any unused cells left blank, =TEXTJOIN(" / ",TRUE,A2:F2) places a slash between the nonblank labels. The range can also be a table column or a longer cell range, provided the formula does not refer to itself.
Choose a separator and preserve the right format
The delimiter is part of the output, so choose it for how the result will be read or reused. Commas are familiar for short lists, while semicolons may be clearer when the entries themselves contain commas. A space works for a full name assembled from separate name parts, but it may make boundaries harder to see if the values contain spaces already.
With first, middle, and last name parts in A2:C2, =TEXTJOIN(" ",TRUE,A2:C2) returns one name with spaces between the present parts. If the middle-name cell is blank, TRUE skips it without leaving two adjacent spaces. This is often simpler than concatenating cells with the ampersand operator, which requires extra handling when some source cells are empty.
For one item per line in the result, use a line break as the delimiter. With entries in A2:A6, =TEXTJOIN(CHAR(10),TRUE,A2:A6) joins the nonblank entries on separate lines within one cell. Select the result cell and choose Home > Wrap Text so the line breaks are visible in the worksheet. The cell may need a taller row to display all lines.
Dates and numbers may need attention because Excel stores them as numeric values and displays them according to formatting rules. A joined result is text, and it may not retain the source cell’s custom display format exactly as expected. If a date must appear in a specific style, convert it deliberately with the TEXT function before joining, or prepare a helper column that contains the desired text. Check the resulting string before using it in a report or export.
Handle cells that look blank but are not
A cell can look empty while containing a space, an apostrophe, or a formula that returns an empty string. Ordinary truly empty cells are the simplest case. A cell containing one or more spaces is not empty to Excel, however, so TEXTJOIN may include it and produce what looks like an extra gap between separators.
To find the cause, click a suspect source cell and inspect the formula bar. You can also test whether a cell contains a space by using a helper formula. With the questionable value in A2, =LEN(A2) returns the number of characters; a result greater than zero means the cell contains something, even if that content is hard to see. A formula returning an empty string may behave like an empty entry for joining, but spaces and other invisible characters are not the same as an empty string.
If unwanted spaces are the problem, clean the source values or use a helper column. With source text in A2, =TRIM(A2) removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. Fill that helper formula down, then point TEXTJOIN at the cleaned helper range. TRIM does not remove every possible nonprinting character, such as some nonbreaking spaces copied from web pages; those may require a targeted cleanup step.
Do not confuse skipping a blank cell with deleting it. TEXTJOIN leaves the worksheet layout and original cells unchanged; it simply omits empty entries in the displayed result. If you need to remove entire blank rows from a dataset, that is a separate cleanup operation and should be done carefully, especially when other columns contain data on those rows.
Use Excel’s Consolidate command for numeric summaries
If the goal is to combine totals from several worksheets or ranges, use the ribbon command rather than TEXTJOIN. In Excel for Microsoft 365 on Windows, select the destination cell, then choose Data > Data Tools > Consolidate. In the dialog, select a function such as Sum, add each source range, and choose whether to use labels in the top row or left column when those labels are present.
For example, if three sheets each contain monthly sales totals in the same layout, Consolidate can sum corresponding numeric cells into a summary range. The command is useful when the source ranges have matching structures, or when you match data using labels. It produces numeric summaries, not a comma-separated list of the source text. If you want a combined list of text entries, TEXTJOIN or a different data-shaping approach is more appropriate.
When linking the result back to the source data matters, review the options in the Consolidate dialog before confirming. The command can create links to source data, but the resulting layout and update behavior differ from a simple formula that refers directly to cells. Keep a copy of the workbook or verify the destination range before running a consolidation over existing content.
Turn the result into a fixed value or reuse it elsewhere
A TEXTJOIN formula is dynamic: edits to the source cells update the joined result. That is helpful for a live report, but a fixed text value may be preferable for a one-time export or for sharing a snapshot. Copy the result cell, then use Home > Paste > Paste Values to replace the formula with its current displayed text. On Windows, Ctrl+C copies; on Mac, Command+C copies. The ribbon path for Paste Values is also available on Mac, though the exact menu presentation can vary by Excel release.
Before converting a formula to a value, confirm that the output is correct and that you will not need it to update. Also consider whether the joined text is meant for people or for later data analysis. Combining many records into one cell can make a report easier to read, but it makes individual entries harder to filter, sort, count, or edit. Keep the original row-by-row data as the source of truth when you may need to analyze it later.
Common errors and practical limits
- Too many separators: Check for spaces or other invisible characters in cells that appear blank. TEXTJOIN skips empty cells when its ignore-empty argument is TRUE, not every cell that merely looks empty.
- The formula appears as text: Check whether the destination cell is formatted as Text, or whether an apostrophe was typed before the equals sign. Change the format to General and re-enter the formula if needed.
- The output is cut off: Excel limits the contents of a cell to 32,767 characters. A very long joined result can exceed that limit, so keep the source data in separate cells or split the output if necessary.
- Unexpected number or date text: Excel may render numeric values differently from their visible cell formatting when creating a text result. Format the values explicitly before joining when the exact representation matters.
- Formula not accepted: Check the function spelling and argument separators. Some regional settings use semicolons instead of commas between formula arguments; follow the separator convention used by formulas in your Excel installation.
TEXTJOIN is available in current Microsoft 365 Excel and in several earlier desktop releases, including Excel 2019. If you are working in an older version that does not recognize the function, use a helper column with concatenation or upgrade the workbook environment. Excel for the web also supports modern formulas, though ribbon placement and dialog presentation can differ from the Windows desktop application.
Frequently Asked Questions
Can Excel combine cells and ignore blanks?
Yes. Use TEXTJOIN with TRUE as its ignore-empty argument. For example, with values in A2:A10, =TEXTJOIN(", ",TRUE,A2:A10) joins the nonempty entries with comma-space separators.
Does Excel’s Consolidate command join text?
No. Data > Data Tools > Consolidate summarizes values from ranges using a selected function, such as Sum or Average. Use TEXTJOIN to combine text entries into a single cell.
How do I join cells with a line break?
With the items in A2:A10, use =TEXTJOIN(CHAR(10),TRUE,A2:A10), then turn on Home > Wrap Text for the result cell. Each nonblank item appears on a separate line inside that cell.
Why does TEXTJOIN leave an extra space or separator?
A cell that looks blank may contain spaces or another character, which is different from an empty cell. Inspect the source or clean ordinary spaces with TRIM in a helper column before joining.
Can I combine cells without a delimiter?
Yes. Set TEXTJOIN’s first argument to an empty text string. With entries in A2:A5, =TEXTJOIN("",TRUE,A2:A5) joins the nonblank contents directly, without inserting punctuation or spaces.
Related
- Excel Data Validation: Create Rules, Lists, and Input Limits
- Excel Data Validation: Create Lists, Rules, and Alerts
- Excel Data Validation: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Splitting Text in a Cell by DelimiterIn Microsoft 365 or Excel 2024, use TEXTSPLIT to divide text from one cell into separate cells. If…
- Excel Formula for Random Selection From a List: GuideTo return one random item from a contiguous list in Excel, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). If your items are in…
- Excel Formula for Percentage Between Two Cells ExplainedTo find what percentage one cell is of another in Excel, divide the first cell by the second:…
- Excel Formula for Last 4 Digits: RIGHT and FixesTo return the last four characters from a value in cell A2, use =RIGHT(A2,4). For example, if A2…
- Excel SUM and AVERAGE: Formulas, Examples, and TipsIn Excel, use SUM to add numbers and AVERAGE to find their arithmetic mean. With values in cells…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.