How to Combine Two Columns in Excel Without Losing Data

Q: How do I combine two columns in Excel?

Use =A2&" "&B2 for two cells with a space between, then fill down. For a whole range use =TEXTJOIN(" ", TRUE, A2:E2) , which also skips blank cells.

Q: How do I add a line break between combined values?

Use CHAR(10) as the delimiter — =TEXTJOIN(CHAR(10), TRUE, A2:D2) — then enable Wrap Text on the column, or the breaks will not be visible.

Q: How do I combine columns without a formula?

Type the first combined result by hand and press Ctrl + E for Flash Fill. The values are static, so they will not update if the source data changes.

To join two cells with a space: =A2&" "&B2

To join a whole range and skip blanks: =TEXTJOIN(", ", TRUE, A2:E2)

Then — the step people miss — copy the result and Paste Special → Values before deleting the original columns. The formula only points at them; delete the source and you get #REF! where your data was.

The four ways, and when each fits

Method	Best for
`&` operator	Two or three cells. Shortest to type.
`TEXTJOIN`	A range, especially with blanks to skip
`CONCAT`	A range where blanks do not matter
Flash Fill	One-off, no formula wanted

The ampersand

=A2&" "&B2 joins first and last name with a space between.

Everything you want literally goes in quotes, so a comma-space separator is =A2&", "&B2. Forgetting the quoted space is the usual reason you end up with "JohnSmith".

It chains as far as you like — =A2&" "&B2&" "&C2 — but past three or four cells it becomes fiddly, and TEXTJOIN is easier.

TEXTJOIN: the one worth learning

TEXTJOIN takes three parts: the delimiter, whether to ignore empty cells, and the range.

=TEXTJOIN(", ", TRUE, A2:E2)

The second argument is why this beats everything else. Set to TRUE, blank cells are skipped entirely. With & or CONCAT, a blank middle cell leaves a stranded separator — "London, , UK". TEXTJOIN with TRUE gives "London, UK".

Set it to FALSE only when you need the empty positions preserved, such as building a fixed-width record.

TEXTJOIN needs Excel 2019 or Microsoft 365. Older versions return #NAME?.

CONCAT and CONCATENATE

=CONCAT(A2:E2) joins a range with no separator at all — fine for codes, wrong for anything readable.

CONCATENATE is the older function. It still works, but it cannot take a range — you must list each cell — and Microsoft treats it as superseded. There is no reason to choose it for new work.

Numbers and dates lose their formatting

The trap that surprises everyone. Joining a formatted cell uses its underlying value, not what you see on screen.

A date displaying "05/09/2026" joins as 46265, because that is the serial number Excel stores. Currency loses its symbol; a percentage becomes a decimal.

Wrap it in TEXT with the format you want:

Date: =A2&" — "&TEXT(B2,"dd/mm/yyyy")
Currency: =A2&": "&TEXT(B2,"£#,##0.00")
Percentage: =A2&" "&TEXT(B2,"0.0%")
Fixed decimals: =TEXT(B2,"0.00")

If a joined value comes out as an unexpected long number, this is why — see Excel dates not recognised as dates for how that serial number works.

Adding a line break

For an address block in one cell, use CHAR(10) as the separator:

=TEXTJOIN(CHAR(10), TRUE, A2:D2)

Then turn on Wrap Text (Home → Wrap Text) for that column. Without it the line breaks exist in the value but the cell shows everything on one line, and it looks as though the formula failed.

Flash Fill, for a one-off

Type the combined result for the first row, then press Ctrl + E. Excel infers the pattern and fills the rest.

No formula, no cleanup — but the values are static and will not update when the source changes. Good for a quick job, wrong for a live sheet.

Making it permanent

This is where data gets lost, so it is worth doing in order:

Select the column of results and copy it.
Select the same column (or wherever the final data should sit).
Right-click → Paste Special → Values.
Only now delete the original columns.

Skip step 3 and every cell becomes #REF! the moment the source columns go, because the formulas were only ever pointing at them.

Frequently asked questions

How do I combine two columns in Excel?

Use =A2&" "&B2 for two cells with a space between, then fill down. For a whole range use =TEXTJOIN(" ", TRUE, A2:E2), which also skips blank cells.

What is the difference between CONCAT and TEXTJOIN?

CONCAT joins a range with no separator. TEXTJOIN lets you set a delimiter and, crucially, choose whether to skip blank cells — which avoids the stranded separators you get from CONCAT or the ampersand.

Why did my date turn into a number?

Joining uses the underlying value, and Excel stores dates as serial numbers. Wrap it: =A2&" "&TEXT(B2,"dd/mm/yyyy").

Why did my data disappear when I deleted the old columns?

The combined column held formulas pointing at those cells, so removing them produced #REF!. Copy the results and use Paste Special → Values first, then delete the originals.

How do I add a line break between combined values?

Use CHAR(10) as the delimiter — =TEXTJOIN(CHAR(10), TRUE, A2:D2) — then enable Wrap Text on the column, or the breaks will not be visible.

Why do I get #NAME? on TEXTJOIN?

Your Excel version predates it — TEXTJOIN needs Excel 2019 or 365. Use the ampersand operator instead, which works in every version.

How do I combine columns without a formula?

Type the first combined result by hand and press Ctrl + E for Flash Fill. The values are static, so they will not update if the source data changes.

Conclusion

& for two or three cells, TEXTJOIN for a range — and TEXTJOIN's ignore-blanks argument is the reason it is worth learning over the alternatives.

Wrap dates and currency in TEXT so they keep their formatting, and always Paste Special → Values before deleting the source columns.

Related: how to split cells in Excel is the reverse operation, and removing spaces covers cleaning text before you join it.