To combine a column into one colon-separated text string in Excel for Microsoft 365, enter =TEXTJOIN(":",TRUE,A2:A6) in a cell outside the source range. If A2:A6 contains red, blue, and green with the other cells blank, the result is red:blue:green; the formula skips empty cells and does not add a colon at the end.
What the formula does
TEXTJOIN combines text from multiple cells or ranges, placing a chosen delimiter between the items it includes. In this example, the colon inside quotation marks is the delimiter, and A2:A6 is the range to combine. The argument TRUE tells Excel to ignore empty cells.
The result is a single text value in the cell containing the formula. It does not change the source column, insert colons into those cells, or split the result into multiple cells. You can copy the finished result elsewhere, or copy the formula down or across if you need separate joined strings for other ranges.
Use a colon as a quoted text character, not as part of the range reference. For example, A2:A6 means the cells from A2 through A6; the quoted colon in the first argument is what appears between the joined values.
Steps to join a column
- Decide which cells belong in the result. A bounded range such as A2:A100 is usually clearer than selecting an entire column.
- Click a blank cell outside that range. Putting the formula inside its own source range can create a circular reference.
- Type the formula, replacing A2:A6 with your actual range:
=TEXTJOIN(":",TRUE,A2:A6). - Press Enter. Check the result for missing items, extra separators, or formatting that needs attention.
- If the source list changes, the formula recalculates automatically. To keep only the current text, copy the result and use Paste Special > Values in the destination.
The instructions and comma-separated formula syntax here are for Excel for Microsoft 365 on Windows. TEXTJOIN is also available in current Excel for Mac and Excel for the web. In some regional settings Excel expects semicolons between formula arguments instead of commas; if Excel rejects a correctly written formula, check the list separator used by your installation.
Choose the right range and delimiter
Use a range that covers the records you actually want. A reference such as A:A can be convenient, but an entire Excel column contains more than a million rows. Processing a very large reference can be inefficient, and it is easy to include a heading, notes, or unrelated values by mistake. A range such as A2:A500 is more controlled when the list has a known size.
If the data is an Excel Table, a structured reference can make the formula easier to maintain as rows are added. Assuming the table is named Table1 and its column is named Code, enter =TEXTJOIN(":",TRUE,Table1[Code]) in a cell outside the table. The formula refers to that named column rather than fixed row numbers.
A colon is useful when another system or person expects a colon-delimited string, but it is not automatically a safe separator for every dataset. If a source value can itself contain a colon, the output may be ambiguous. For instance, north:west:east does not reveal whether the source was three values or two values where one contained a colon. Choose a delimiter that is absent from the data, or agree on an escaping or quoting rule with the system that will read the text.
How blank cells affect the result
With the second argument set to TRUE, genuinely empty cells are skipped, so the delimiter appears only between included values. Cells containing a formula that returns an empty string are generally treated as empty for this purpose. Cells containing a space are not empty, however; they can produce what looks like an extra separator or a blank-looking item.
To see the alternative, assume A2 contains north, A3 is empty, and A4 contains south. This formula includes the empty position, producing north::south: =TEXTJOIN(":",FALSE,A2:A4). That may be necessary when each row represents a position and empty positions must be preserved, but it is usually not what readers mean by joining the nonblank values.
If spaces or unwanted entries are causing trouble, clean the source data before joining it. Inspect cells that look blank, remove accidental spaces, and decide whether zeros, labels, or formula-generated empty strings should count as values. TEXTJOIN does not decide what is meaningful data; it follows the cells and options in the formula.
Numbers, dates, and displayed formatting
Joining a numeric column returns text, so the result is no longer a set of separately usable numeric cells. Also, do not assume that the joined text will preserve each cell’s number format. A date displayed as 2-Nov-2026 in the source, for example, may appear as its underlying serial number when combined. Fixed decimal places, currency symbols, and percentage formatting can likewise need to be applied deliberately.
For a numeric range where every value should show two decimal places, and A2:A4 contains 2, 3.5, and 0.4, use =TEXTJOIN(":",TRUE,TEXT(A2:A4,"0.00")). The result is 2.00:3.50:0.40. This array-style use of TEXT works in Microsoft 365; it formats each value before TEXTJOIN combines the results.
For a date-only range, and with dates in A2:A4, use =TEXTJOIN(":",TRUE,TEXT(A2:A4,"yyyy-mm-dd")) to produce consistently formatted dates such as 2026-11-02. Pick a format suitable for the people or software receiving the output. If the column mixes dates, numbers, and ordinary text, format or transform the different types separately rather than applying one number format indiscriminately.
Options for older Excel versions
TEXTJOIN is available in Excel 2019 and later, including Microsoft 365. In an earlier Excel version that does not recognize it, a short fixed list can be joined with the ampersand operator. Assuming A2, A3, and A4 contain three values and none is blank, use =A2&":"&A3&":"&A4. This approach becomes cumbersome as the list grows and includes separators around blank cells unless you add extra logic.
For a longer list in older Excel, a cumulative helper column is another option. Assuming the source values start in A2, enter =IF(A2="","",A2) in B2. In B3, enter =IF(A3="","",IF(B2="",A3,B2&":"&A3)), then fill B3 down alongside the source list. The last helper cell contains the accumulated text, with blank source rows skipped. This method is less convenient than TEXTJOIN and repeatedly builds the growing string, so use it for modest lists rather than very large ones.
CONCAT can combine text but does not provide TEXTJOIN’s delimiter and ignore-empty options in one function. TEXTSPLIT goes in the opposite direction: in Microsoft 365 or Excel 2024, it can split delimiter-separated text into cells. Use it when you need to undo a join, not to create the colon-separated string in the first place.
Common errors and practical limits
- #NAME? Usually means the Excel version does not recognize TEXTJOIN, the function name is mistyped, or the installation requires a different argument separator.
- #VALUE! Check for an error value in the source range and for a result that is too long. Excel limits a cell’s contents to 32,767 characters, so a joined string that exceeds the limit cannot fit in one cell.
- A circular-reference warning: Move the formula outside the source range. If the formula is in A2 while it reads A2:A6, it is asking Excel to use its own result as an input.
- Unexpected double colons: Check whether the formula uses FALSE, whether values are spaces rather than truly blank, and whether empty placeholders need to be retained.
- Dates or amounts look wrong: Apply an explicit TEXT format to the source values before joining, or create a helper column with the intended display text.
For a very long list, consider whether one cell is the right output format. The 32,767-character cell limit is a hard constraint, and a long colon-delimited string can also be difficult to inspect or parse. Keeping records in rows is usually more useful for sorting, filtering, calculations, and later updates.
Frequently Asked Questions
How do I add a colon between every nonblank cell?
Use =TEXTJOIN(":",TRUE,A2:A100), changing the range to match your data. TRUE skips empty cells, and Excel places the colon between the remaining values rather than after the final value.
How do I keep empty cells as empty positions?
Change the second argument to FALSE, as in =TEXTJOIN(":",FALSE,A2:A10). Empty cells then contribute separators between positions. This is useful when the position of a blank matters, but it can make the result look like it contains extra delimiters.
Can I put a colon after the last value too?
TEXTJOIN normally places delimiters only between items. If you deliberately need a trailing colon and the joined result is not empty, append one with =TEXTJOIN(":",TRUE,A2:A6)&":". A trailing delimiter can confuse systems that expect a separator only between fields, so confirm the receiving format first.
Can I join a whole column?
You can refer to A:A, but a bounded range or a Table column is often easier to manage and avoids processing an unnecessarily large area. Make sure the heading is not included unless it belongs in the output, and keep the formula cell outside the referenced column.
Does the formula change the original cells?
No. It creates a text result in the formula cell and leaves the source values as they are. If you need a fixed text snapshot, copy the result and paste values; if you want it to update when the source changes, keep the formula.
Related
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Variance and Box Plots in Excel: What Each ShowsExcel’s Box and Whisker chart does not show variance by default. Create the chart to compare medians, quartiles,…
- Excel Attendance File to Print: Set Up and Print ItTo print an Excel attendance file clearly, open it in Excel for Microsoft 365 on Windows, select the…
- Count Excel Without Blanks: COUNTA and Other MethodsTo count cells that are not blank in Excel, use =COUNTA(A2:A20), replacing A2:A20 with your range. COUNTA counts…
- Excel Find and Replace Shortcut: Ctrl+H ExplainedIn Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace directly on the Replace…
- How to Remove a Section Break in Microsoft Word on MacTo remove a section break in Word on Mac, show formatting marks, click at the beginning of the…
- How to Add Footnotes at the End of a Page in Microsoft WordTo add a footnote at the bottom of a page in Microsoft Word, place the cursor after the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.