How to Combine Two Columns in Excel Without Wrecking Dates or Account Codes

How-to · Updated July 2026 · ~13 min read

Every accounting export splits things you need together. The trial balance arrives with the account code in column A and the account name in column B, but the report wants 1000 - Cash in one cell. Payroll gives you first name and surname in separate columns. The customer address is spread across five columns and has to fit on one invoice line. So you write the obvious formula — =A2&" "&B2 — and Excel obliges by turning 25/08/2026 into 46259 and account code 0042 into 42.

Neither is a bug. Both have a two-second fix, and it is the same fix — TEXT() — so this post covers it first, because every combining method that follows inherits the same problem. Then five methods in increasing order of durability: the ampersand, CONCAT, TEXTJOIN, Flash Fill and Power Query, each on real accounting data.

One scope note before starting. This post is about combining two columns inside one sheet. Stitching several exports or workbooks into a single table is a different job with different tools — that walkthrough is merging multiple Excel files into one. And Merge & Center (Home tab, Alignment group) is neither job: it merges cells for layout and keeps only the upper-left value, deleting everything else. Excel even warns you. Never point it at data.

The two things concatenation destroys

Dates become serial numbers

Excel stores a date as a count of days since 1 January 1900; the format you see — 25/08/2026 — is a display layer on top of the stored number, which for that date is 46259. The & operator, and every function in this post, reads the stored value, not the display. So ="Paid "&B2 produces Paid 46259, and it will do so identically in CONCAT and TEXTJOIN.

The fix is to convert the date to text yourself, in the format you choose, before joining:

="Paid "&TEXT(B2,"dd/mm/yyyy")

TEXT(value, format_text) takes the same format codes as Format Cells, so "dd mmm yyyy" gives 25 Aug 2026 and "mmm-yy" gives Aug-26 for period labels.

Leading zeros vanish

If your account codes, cost centres or employee IDs start with zero — 0042, 0300 — and the column is right-aligned, Excel is storing them as numbers. The zero you see comes from a custom number format such as 0000; the stored value is 42. Concatenate it and you get 42, because again the formula reads the value, not the format.

Same fix, with a digit mask instead of a date mask:

=TEXT(A2,"0000")&" - "&B2

TEXT(42,"0000") returns 0042, so this also repairs codes whose zeros were already stripped at import — provided every code is the same width. If your codes are stored as text (left-aligned, often with a green error triangle), the zeros are genuinely in the cell and pass through & untouched; no fix needed. The deeper cure is importing code columns as text in the first place, which is covered in cleaning messy data in Excel.

Formatting of every kind is lost the same way, so here is the short table to keep:

With that in hand, the methods.

Method 1: the ampersand — works in every Excel ever shipped

For two columns, & is usually the right answer: no version gate, no function to remember, and you can see exactly what is being joined.

Payroll full names, first name in A, surname in B, with a space between:

=A2&" "&B2

That space is a text literal in quotes — the answer to the perennial "how do I combine 2 columns with a space" question. Any literal goes the same way: ", " for comma-space, " - " for the accounting label separator.

Account labels from a chart of accounts export, code in A, name in B:

=TEXT(A2,"0000")&" - "&B2

gives 1000 - Cash, 0042 - Petty cash float, and so on down the column. Enter it in the first row and double-click the fill handle (the small square at the cell's bottom-right corner) to fill to the bottom of the adjacent data; inside a proper Excel Table it fills itself.

Composite keys are the other daily use: joining two or three columns into one string that identifies a row, so duplicates show up.

=A2&"|"&TEXT(C2,"yyyy-mm-dd")&"|"&TEXT(D2,"0.00")

builds supplier|date|amount, and a COUNTIF on that column finds double-entered invoices even when no single column repeats. The pipe separator prevents false joins (12|34 and 123|4 stay distinct where 1234 would collide). What to do once you have found them is removing duplicates in Excel.

The ampersand's one weakness is verbosity: joining ten columns means nine &"?"& interludes. That is what the next two functions are for.

Method 2: CONCAT, and where CONCATENATE fits

CONCAT joins everything you hand it, and unlike the ampersand it accepts ranges:

=CONCAT(A2:B2)

The catch is visible in the output: 1000Cash. CONCAT has no delimiter argument — it butts values together, and you cannot put a space inside the range. So for two columns with a separator you are back to interleaving literals, =CONCAT(A2," - ",B2), at which point the ampersand was shorter. CONCAT earns its keep when you have many contiguous text columns and genuinely want no separator — rebuilding a reference string that an export split, say.

Two version notes. CONCATENATE is the legacy version — Microsoft keeps it for compatibility with old workbooks, but it does not accept ranges and there is no reason to use it in new work. And CONCAT itself (with TEXTJOIN, next) shipped in Microsoft 365 and, as of 2026, is present in the Excel 2019, 2021 and 2024 perpetual releases — but not in Excel 2016 perpetual, despite 2016 being newer than CONCATENATE. On 2016, the ampersand is your everything. Availability by release does get revised, so if you are writing for a mixed-version team, check Microsoft's current function-availability page — or simply type the function on the machine in question and see whether it returns #NAME?.

Both traps from the first section apply unchanged: CONCAT reads stored values, so dates and leading zeros need TEXT() exactly as before.

Method 3: TEXTJOIN — built for messy ERP exports

TEXTJOIN(delimiter, ignore_empty, text1, …) fixes both of CONCAT's gaps at once: the delimiter is typed once and applied between every pair, and ignore_empty handles the blanks that every real export contains.

The showcase is the one-line invoice address. Address 1, Address 2, City, Region and Postcode sit in B through F, and Address 2 is blank on half the rows. The ampersand version produces 12 Marina Walk, , Dubai, , 00000 — double separators wherever a field is empty. TEXTJOIN with ignore_empty set to TRUE skips them:

=TEXTJOIN(", ", TRUE, B2:F2)

gives 12 Marina Walk, Dubai, 00000 on the sparse rows and the full five-part address where everything is present. No IF ladder, no cleanup pass.

Details worth knowing before you rely on it:

• A space is not empty. ignore_empty skips truly empty cells and cells whose formula returned "" — but a cell containing a single space is text, and TEXTJOIN will faithfully join it, giving , , back again. If an export pads empty fields with spaces, run TRIM first (or wrap each argument), part of the wider cleanup in cleaning messy data.
• The traps still apply. A date column inside the range comes out as serial numbers. Mix TEXT() into the argument list where needed: =TEXTJOIN(" | ", TRUE, A2, TEXT(C2,"dd/mm/yyyy"), TEXT(D2,"#,##0.00")).
• There is a ceiling. A cell holds at most 32,767 characters; if the joined result exceeds that, TEXTJOIN returns #VALUE!. You will only hit it joining whole columns, but that is exactly what =TEXTJOIN(",",TRUE,A:A) does, so join ranges, not columns.
• Same version gate as CONCAT: Microsoft 365, and the 2019, 2021 and 2024 perpetual releases — not 2016 perpetual, as of 2026.

Method 4: Flash Fill — fastest for a one-off, and completely static

Flash Fill (Excel 2013 onwards) skips formulas entirely: you type what you want the first result to look like, and Excel infers the pattern from your example.

1. In C2, next to Sara and Haddad, type the result you want: Sara Haddad.
2. In C3, start typing the next one — after a character or two Excel usually shows the whole column as a grey preview; press Enter to accept it.
3. If no preview appears, select C2 and press Ctrl+E, or use Data tab > Data Tools group > Flash Fill.

It handles more than joining — S. Haddad from the same two columns, or extracting a code out of a combined string — because it is pattern inference, not concatenation. That is also its weakness. Three things to know:

• The output is values, not formulas. Edit a surname in B and column C does not change. Add rows and nothing fills. For a report you refresh monthly, that means redoing it monthly — use a formula or Power Query instead.
• It guesses. Give it Sara Haddad as the only example and it cannot know whether row 7's double-barrelled surname or middle name should behave the same way. Scan the filled column before trusting it, especially past the first screen.
• It needs an adjacent column in the same contiguous block as the source data, and it fills down only as far as that block runs.

For a hundred rows you need once, it is unbeatable. For anything recurring, keep reading.

Method 5: Power Query — the version that survives next month's export

If the same export lands on your desk every month, combine the columns in a query, so the work re-runs on refresh instead of being redone by hand. Power Query is built into Excel 2016 and later (Data tab > Get & Transform Data); if the editor is new to you, start with Power Query for beginners.

1. Click anywhere in the data and use Data > From Table/Range. Excel converts the range to a Table if it is not one and opens the Power Query Editor.
2. Click the first column header, then Ctrl-click the second — selection order controls join order, so click the code column before the name column.
3. Transform tab > Text Column group > Merge Columns replaces the two columns with the merged one. If you want to keep the originals, use Add Column tab > Merge Columns instead — same dialog, different outcome.
4. In the dialog, set Separator to Custom and type - (space, hyphen, space), name the new column Account Label, and click OK.
5. Home > Close & Load puts the result on a new sheet. Next month, paste the new export into the source table and Data > Refresh All re-runs everything.

Power Query handles the date trap better than formulas: when a selected column is typed as date, the merge step converts it to text through your locale first, so you get 25/08/2026 in the output, not 46259. The generated M shows the mechanism:

= Table.CombineColumns(
    Table.TransformColumnTypes(Source, {{"Account Code", type text}}, "en-GB"),
    {"Account Code", "Account Name"},
    Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),
    "Account Label")

Leading zeros need one deliberate step, because a code Excel stored as the number 42 arrives in Power Query as 42 — converting it to text at that point gives "42", not "0042". Re-pad it before merging with a custom column (Add Column > Custom Column):

Text.PadStart(Text.From([Account Code]), 4, "0")

Text.PadStart left-pads to the width you give it, so every code comes out four digits. If your source is a CSV that Power Query imports directly, there is a cleaner fix: set the code column's type to Text in the import step (click the type icon in the column header, choose Text, then Replace current in the dialog that appears) and the zeros never get stripped at all.

Lock in the results before deleting the source columns

A formula in C that references A and B returns #REF! the moment you delete A and B — a classic Friday-afternoon mistake on a finished report. Convert formulas to values first: select the combined column, Ctrl+C, then right-click > Paste Options > Values (in current Microsoft 365 builds, Ctrl+Shift+V pastes values directly). Then delete the source columns. Flash Fill output is already values; Power Query output lives in its own table, so the loaded sheet is safe, but deleting columns from the source table breaks the query instead — remove them inside the editor if you must.

How HISAB 360 helps

The methods above are teachable, but the fifth time you describe the same fix — "join the code and name with a hyphen, pad the codes to four digits, format the dates British-style, skip the blank address lines" — you start wishing you could just say that to Excel. HISAB 360 is a paid Excel add-in for Windows desktop Excel (Microsoft 365 / 2016+) that puts an AI assistant in a panel inside the workbook. It reads the sheet you are looking at, so you can describe the messy export in plain English and it writes the TEXTJOIN/TEXT formulas into the columns you point at — or, for the recurring case, builds the Power Query steps (the merge, the type-to-text, the Text.PadStart) as a query you can open, inspect and refresh next month.

Because it also connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting — reading and writing — the account codes and names often do not need to arrive via CSV at all: it pulls the chart of accounts, invoices and ledgers straight into sheets, already typed. There is a 15-day free trial with 50 AI credits and no card required. Honest limits: Windows desktop Excel only — no Mac, no Excel on the web — and it is paid software after the trial, from $7/month.

Choosing a method

Whichever you choose, the two traps travel with you: TEXT() for dates and zero-padded codes in formulas, typed-as-text columns or Text.PadStart in Power Query, and a careful eye on Flash Fill's guesses.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.