If you're still typing long, comma-heavy CONCATENATE formulas to merge text in Excel, you're doing more manual work than necessary.Since switching to four alternatives that make combining data faster, cleaner, and far less frustrating, I haven't looked back.CONCATENATE no longer fits modern Excel workflows It can't handle ranges or ignore empty cells cleanly CONCATENATE still works in Excel, but it hasn't kept up with how people build spreadsheets today.
Its biggest limitation is that it can't handle ranges.For example, if you try: =CONCATENATE(A2:D2) Excel evaluates the range as individual cell values (A2, B2, C2, D2) rather than treating it as a single input range, so you don't get a combined result.Instead, to combine multiple cells, you need to reference each one individually, like: =CONCATENATE(A2, B2, C2, D2) That might not sound too bad at first, but it quickly becomes tedious as datasets grow.
Every additional cell adds more typing, more chances for mistakes, and more formula clutter.CONCATENATE also doesn't handle separators automatically, so if you build formulas that include manual delimiters, empty cells can result in unintended spacing or extra separators.Over time, I found myself spending more effort maintaining CONCATENATE formulas than actually working with the data they produced.
That was the point where I started looking for better options.Related Stop wrestling with text in Excel: These 8 tools are game-changers Fix "fake" numbers, strip hidden web spaces, and join text professionally using Excel's built-in legacy and modern tools.Posts 1 By Tony Phillips CONCAT is the modern upgrade to CONCATENATE Use it for range-based merging Even though CONCATENATE is still available across recent versions of Excel, including Microsoft 365, Excel 2021, and Excel 2024, Microsoft recommends CONCAT as the modern alternative and notes that CONCATENATE is maintained for compatibility and may be deprecated in future versions.
However, the good news is that because CONCAT uses nearly identical syntax, I found it an easy upgrade that didn't require me to learn an entirely new way of working.Using the same dataset as in the example above, you would type this formula and press Enter: =CONCAT(A2:D2) The result is a single combined string made from all values in the selected range, without needing to reference each cell individually.This means that if you insert a column within the range, Excel automatically includes the new cells.
However, CONCAT doesn't allow delimiters, so everything is joined exactly as-is.If you need separators like spaces or commas, you'll need a different approach.This is where TEXTJOIN can be more useful.
Microsoft 365 Personal OS Windows, macOS, iPhone, iPad, Android Free trial 1 month Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.$100 at Microsoft Expand Collapse TEXTJOIN handles formatting automatically for large ranges Apply consistent separators without manual work TEXTJOIN lets you define a delimiter once and apply it across an entire range, which makes it useful when you need readable, structured results instead of raw concatenation.It also gives you the option to ignore blanks.
For example, suppose you want to combine the dishes listed in cells B2 through D2 into a single readable string, separated by commas, with blank cells ignored.Here's what you need to type in E2: =TEXTJOIN(", ",TRUE,B2:D2) where is the delimiter, tells Excel to ignore blank cells, and is the range containing the values to join.When you press Enter, all values are combined into a clean comma-separated string.
Notice how, for the blank cells, the delimiter isn't repeated—Excel simply skips over them and moves to the next valid value.Related CONCAT vs.TEXTJOIN: Which Should You Use in Excel? Decide how to link your data together.
Posts 5 By Tony Phillips The ampersand operator offers precise control for small merges Use it when full functions are unnecessary Not every text-merging task needs a full function.For small, one-off combinations, I often skip formulas entirely and use the ampersand (&) operator instead.Unlike CONCAT or TEXTJOIN, the ampersand works inline and gives you precise control over exactly what appears between values.
Imagine you want to combine a last name in B2 and a first name in A2 into a single full name, separated by a comma and a space.Flash Fill can handle this kind of transformation, but it produces static results that don't update when the source data changes.On the other hand, the ampersand operator keeps everything dynamic, so any changes to the names automatically update the combined result.
To use it: Select the output cell.Type =B2 (the last name).Add &", "& to insert a comma and space.
Add A2 to concatenate the first name.Press Enter.Here's how your formula should look: Deals Save on productivity software deals and Excel tools Discover discounts on Office suites, cloud storage, Excel add-ins, and productivity subscriptions—plus deals on templates and training that speed up formulas and Power Query workflows.
Compare offers to get the best savings on software and productivity to Deals Explore Software, AI & Subscriptions Deals =B2 & ", " & A2 Now, the last and first names are combined into a properly formatted, dynamic full-name string in a single cell, and it will automatically update if either source cell changes.Related The Ultimate Glossary of Microsoft Excel Symbols You don't truly understand Excel until you know its symbols.Posts 2 By Tony Phillips Power Query handles text merging at the data-processing level Combine columns outside the grid When I'm working with datasets that are likely to grow or need repeated cleanup, I avoid formulas entirely.
Power Query lets you apply transformations outside the worksheet grid itself, before the data is loaded back into Excel.This works for both imported datasets and datasets you build manually.After formatting your dataset as an Excel table (Ctrl+T), you can use Power Query as a scalable transformation layer.
To load your data into Power Query: Select any cell in the table.Open the Data tab.Select From Table/Range.
Inside the Power Query Editor, you work with a structured view of your data where transformations apply to entire columns, not individual cells.For example, suppose you have a list of first names in column A and last names in column B, and you want to create a full name column: Hold Ctrl while selecting the columns you want to combine.Right-click and choose Merge Columns.
Choose the separator.In this case, a space works well.Name the new column.
When you click OK, the preview updates to show the merged result.Finally, click Close & Load in the top-left corner to send the clean column to a new sheet in your workbook.If you later add, remove, or change any names in the original source data, you can simply click Data > Refresh All to reapply the same transformation steps and update the output.
Tailor your workflow to your needs Some older Excel functions—like SUMPRODUCT, SUBTOTAL, and INDEX/MATCH—still have their place in more advanced or specialized workflows, as they can solve problems that newer functions don't always address as directly.However, keeping up with modern Excel functions—like CONCAT instead of CONCATENATE, XLOOKUP instead of VLOOKUP, and RANK.EQ instead of RANK—makes a noticeable difference in day-to-day work by reducing friction and making spreadsheets easier to read, build, and maintain.
Read More
