Fastest Formula: Ampersand
=A2&" "&B2This combines A2 and B2 with a space in between. It is the quickest formula for first name + last name, city + state, or category + code.
Use CONCAT for Simple Joining
=CONCAT(A2," ",B2)CONCAT is the modern replacement for CONCATENATE. It is easier to read when joining several pieces of text, but it does not automatically ignore blanks.
Use TEXTJOIN When Blanks Matter
=TEXTJOIN(" ",TRUE,A2:B2)TEXTJOIN is usually the best method. The first argument is the separator, the second argument tells Excel to ignore empty cells, and the remaining arguments are the values to join. This prevents awkward double spaces when one column is blank.
Combine Columns with Commas
=TEXTJOIN(", ",TRUE,A2:C2)This is useful for addresses, category paths, tags, and report labels. Because TEXTJOIN can ignore blanks, it handles incomplete address fields better than a manual formula.
Combine Two Columns and Keep the Result
After creating the formula, copy the result column, right-click the same place, and choose Paste Values. This replaces formulas with final text. Do this before deleting the original columns, otherwise the combined formula may break.
Flash Fill Method
Type the desired combined result in the first row, then press Ctrl + E. Excel tries to detect the pattern and fill the remaining rows. Flash Fill is good for quick one-time cleanup, but formulas are better when the source data will change.
Common Mistakes
- Forgetting to add spaces or separators between columns.
- Deleting source columns before pasting formulas as values.
- Using CONCATENATE in new workbooks when TEXTJOIN would handle blanks better.
- Combining numbers that should stay numeric, such as prices or quantities.