How to Combine Two Columns in Excel With a Space

Combine two Excel columns with a space using &, CONCAT or TEXTJOIN, then fill down. See Flash Fill, Power Query, and fixes for blanks and lost formatting.

Contents

Don't Miss the Good Stuff

Get tech news that matters delivered to your inbox.

To combine two columns in Excel, such as first names in A and last names in B, type =A2&" "&B2 in C2, press Enter, then fill the formula down the column. The & sign joins the two cells and the space inside the quotes keeps the words apart, so Ava in A2 and Stone in B2 become Ava Stone. Joining text like this is called concatenating, and the same formula works for any two cells.

Keep the result in its own column, because the formula reads A and B every time it calculates. For blank cells use TEXTJOIN, for fixed text use Paste Values or Flash Fill, and for repeat imports use Power Query. Two things surprised us while building the examples: Flash Fill did not show the preview Microsoft describes, and Power Query's table box started with the wrong setting for our list.

The short version

  • Fastest formula: type =A2&" "&B2 in C2, beside the first row of names, and press Enter.
  • Every row: select the result cell and the cells below it, then press Ctrl + D.
  • Blank cells: =TEXTJOIN(" ",TRUE,A2:B2) skips empty cells instead of leaving stray spaces.
  • Keep the text: copy the results and use Paste Values before you delete the original columns.
  • No formula: type one full example, then press Ctrl + E for Flash Fill.

Combine two columns with the ampersand

Our sheet had first names in column A and last names in column B, with headers in row 1, so the first pair to join was A2 and B2. If you are starting from a blank sheet, our Excel for beginners guide shows how to create a workbook and enter a small table like this one.

Click C2, the empty cell beside the first row of names rather than the header row, and type =A2&" "&B2. You can also type the equals sign, click A2, type &" "& and click B2. Press Enter, and the cell shows the joined text, which was Ava Stone on our sheet.

Excel formula =A2&" "&B2 typed in C2, then full names filled down column C.
Click to expand
The first name sits in A2 (1) and the last name in B2 (2). Typing =A2&" "&B2 in C2 (3) and filling it down gives Ava Stone, Leo Park and Mia Quinn (4).

The space in quotes is the part people miss. Excel puts nothing between the values it joins, so anything you want between them, whether a space, a comma or a word, has to be typed inside double quotes.

Type a header such as Full Name above the result before you go on. It keeps the column readable, and it matters if you later use Flash Fill, which filled an empty header cell on our sheet. The result is still a formula that reads A2 and B2, which is why the section on keeping the results comes before deleting anything.

Ways to combine columns in Excel at a glance

MethodWhat to enterWhat we gotUpdates when the names change
The & sign=A2&" "&B2Ava StoneYes, it is a formula
CONCAT=CONCAT(A2," ",B2)Ava StoneYes, it is a formula
TEXTJOIN=TEXTJOIN(", ",TRUE,E2:E4)Red, Blue, with the empty cell skippedYes, it is a formula
CONCATENATE=CONCATENATE(A2," ",B2)Ava StoneYes, it is a formula
Flash Fill, Ctrl + EAva Stone typed in C2, then Ctrl + ELeo Park and Mia Quinn as plain textNo
Power QueryData > From Table/Range, then Merge ColumnsA Merged column on a new sheetWhen you refresh the query

Use the & sign for quick joins, TEXTJOIN when some cells are empty or many cells need a separator, and Flash Fill or Paste Values when the result should stop being a formula. Power Query suits a list you import again and again. A joined column also makes a good lookup key, which is how you can match on two columns with VLOOKUP.

Add a comma or another separator

Whatever sits inside the quotes between the two cell references ends up between the values. To put a comma and a space between them, type a comma and a space inside the quotes instead of the space alone. A hyphen, a slash or a word such as "and" works the same way.

Leave the quotes out and the values run together. When we typed =A2&B2 in M2, the cell showed AvaStone. Editing it to =A2&" "&B2 gave Ava Stone.

Excel cell M2 showing AvaStone without a separator, then Ava Stone with one.
Click to expand
With no separator, =A2&B2 (1) runs the names together as AvaStone (2). Adding a space in quotes, =A2&" "&B2 (3), gives Ava Stone (4).

Use straight double quotes around the separator. A word typed without quotes is read as a name, and Microsoft says #NAME? in these formulas usually means quotation marks are missing, which the fixes section below shows on our sheet.

Combine columns with the CONCAT function

CONCAT is the function version of the & sign. Each value is its own argument, separated by commas, so =CONCAT(A2," ",B2) joins A2, a space and B2. It returned Ava Stone on our sheet, exactly like the & formula.

If you would rather fill in boxes, click the result cell and press Shift + F3, or select Insert Function at the left end of the Formulas tab. We searched for CONCAT, and Excel listed CONCAT, CONCATENATE and TEXTJOIN together.

The Function Arguments box gives each value its own Text box. We typed A2, a space in quotes and B2 into the first three, and the box showed Formula result = Ava Stone before we clicked OK.

Excel Insert Function search for CONCAT, its Function Arguments box and the result in D2.
Click to expand
Search for CONCAT in Insert Function (1) and Excel lists CONCAT, CONCATENATE and TEXTJOIN (2). In Function Arguments, enter A2, a quoted space and B2 (3), and Formula result shows Ava Stone (4) before you click OK. The cell holds =CONCAT(A2," ",B2) (5) and shows the result in D2 (6).

CONCAT also accepts a whole range, but it never adds a separator for you. Microsoft's help page says it doesn't provide delimiter or IgnoreEmpty arguments, so the values in a range are joined with nothing between them. For a range with spaces or commas between the values, use TEXTJOIN instead.

Microsoft says CONCAT and TEXTJOIN are available on Windows or Mac if you have Office 2019 or an Office 365 subscription. If a workbook will be opened in an older copy of Excel, the & sign is the safer choice.

Skip blank cells with TEXTJOIN

TEXTJOIN puts a separator between every value and can skip empty cells, which the & sign and CONCAT cannot do. It takes the separator first, then TRUE or FALSE, then the cells. Excel's ScreenTip spells it out as TEXTJOIN(delimiter, ignore_empty, text1, [text2], [text3], ...).

Our Color column held Red, an empty cell and Blue in E2:E4. =TEXTJOIN(", ",TRUE,E2:E4) returned Red, Blue, while the same formula with FALSE returned Red, , Blue and kept a slot for the empty cell.

Excel TEXTJOIN formulas with TRUE and FALSE joining a color list with an empty cell.
Click to expand
With TRUE, TEXTJOIN skips empty cells (1), so the empty E3 (2) leaves no gap and F2 shows Red, Blue (3). With FALSE it keeps them (4), and F3 shows Red, , Blue (5).

For two name columns where some rows have no last name, use =TEXTJOIN(" ",TRUE,A2:B2). Microsoft says TRUE ignores empty cells, so a row with one name gets no stray space at the end.

Concatenate with CONCATENATE in older workbooks

CONCATENATE is the older name for the same job, and it still works. =CONCATENATE(A2," ",B2) returned Ava Stone on our sheet, and Excel for Microsoft 365 still lists it beside CONCAT.

Microsoft keeps it for backward compatibility but recommends CONCAT, warning that CONCATENATE may not be available in future versions of Excel. Leave it alone in workbooks that already use it, and write new formulas with CONCAT or the & sign.

Fill the formula down and keep the results

One formula fills one row. To repeat it for the whole list, select the result cell and the cells below it, then choose Home > Fill > Down in the Editing group, or press Ctrl + D. Both filled Leo Park and Mia Quinn on our sheet.

Excel moves the row numbers as it fills, so C4 held =A4&" "&B4 rather than a copy of the C2 formula. Microsoft also describes dragging the fill handle, the small square at the bottom right of the cell, as a quick way to copy a formula down.

Excel Home tab Fill menu with Down highlighted, and the filled formula in C4.
Click to expand
Select C2:C4 (1), open Home > Fill (2) and choose Down (3). Excel adjusts the row numbers, so C4 holds =A4&" "&B4 (4).

Ctrl + D and Ctrl + E, the Flash Fill shortcut, are two useful Excel keyboard shortcuts when you clean up lists often. Ctrl + D fills only the cells you select, so select the result cell and the cells below it first. Ctrl + E fills down the column beside your data, and on our sheet it also changed the empty header cell above the one we selected, so check the top of the column after using it.

Joined names can be wider than the result column. If they spill into the next cell or get cut off, widen the column or wrap text in Excel so the whole result stays visible.

The combined cells are still formulas, so deleting column A or B breaks them. To keep the names as plain text, select the results, choose Home > Copy, then Home > Paste and the first icon under Paste Values, in the same place or another column.

We copied a CONCATENATE result from G2 and pasted it into G3 that way. The formula bar for G3 showed only Ava Stone, with no formula behind it. The right-click menu has Paste Options icons and Paste Special as well.

Excel CONCATENATE formula in G2, the Paste Values menu, and the pasted text in G3.
Click to expand
G2 holds =CONCATENATE(A2," ",B2) (1) and shows Ava Stone (2). After copying it, open Home > Paste (3) and choose the first icon under Paste Values (4). With G3 selected (5), the formula bar shows plain text, no formula (6).

Only delete the original columns after the pasted values look right. On a large sheet, make a copy of the Excel file first so you can compare the two if anything goes missing. A full-name column also makes it easy to spot repeated people when you remove duplicates in Excel.

Keep percentages and number formats readable

Joining a formatted number drops its format. Our H2 showed 40% as a percentage, yet ="Rate: "&H2 displayed Rate: 0.4, because Excel joined the stored value rather than what the cell showed.

Wrap the number in TEXT with the format you want. ="Rate: "&TEXT(H2,"0%") displayed Rate: 40%. Microsoft's help uses the same pattern for dates, with TEXT around the date cell and a date format in quotes, joined with the & sign.

Excel joining a 40% cell shows Rate: 0.4, while the TEXT version shows Rate: 40%.
Click to expand
H2 holds 40% (1). Joining it directly with ="Rate: "&H2 (2) shows Rate: 0.4 (3), while ="Rate: "&TEXT(H2,"0%") (4) shows Rate: 40% (5).

Remember that the joined result is text. Microsoft notes that when you combine numbers and text, the numbers become text and no longer work as numeric values, so keep the original number column for any sums.

Combine names without a formula using Flash Fill

Flash Fill copies a pattern you type instead of using a formula. Type the first full name in the result column, select that cell, and press Ctrl + E, or choose Data > Flash Fill. Excel fills the rest of the column as plain text.

Microsoft's help says a preview appears as you type the second name, and that you accept it with Enter. On our PC it did not appear. We typed Ava Stone in C2, then Le and Leo P in C3, waited a few seconds each time, and got no grey suggestion.

The setting was on even though no preview appeared. File > Options > Advanced showed Automatically Flash Fill ticked under Enable AutoComplete for cell values, and Ctrl + E filled the cells at once, so treat the shortcut as the normal route rather than a fallback.

Excel Flash Fill filling names with Ctrl+E, including the empty header cell, and its option.
Click to expand
No preview appeared while typing the second name (1). Ctrl+E filled the empty header with First Last (2) as well as Leo Park and Mia Quinn as text (3), and the status bar read Flash Fill Changed Cells: 3 (4). Automatically Flash Fill was on in Excel Options (5).

Watch the header row. Our result column had no header, and Ctrl + E filled the empty C1 with First Last as well as filling Leo Park and Mia Quinn, while the status bar read Flash Fill Changed Cells: 3. Type your header first, and check the top of the column after Flash Fill runs.

Flash Fill results are plain text and do not update. If you correct a name in column A later, run Flash Fill again or fix that cell by hand, and use a formula instead if the names change often.

Merge two columns with Power Query

Power Query is worth the extra steps for a list you import again and again. Microsoft says that when you refresh a query, each step runs automatically, so the join happens again without any retyping.

Select the names with their headers, then choose Data > From Table/Range. Excel opens a Create Table box first, and for our all-text list My table has headers was not ticked.

Tick it before you click OK. When we accepted the box as it was, Power Query named the columns Column1 and Column2 and treated First and Last as a row of data, and Excel added a Column1 header above the names on the sheet.

Excel Create Table box with My table has headers unticked, the result, then ticked.
Click to expand
With the names and their headers selected (1), Create Table had My table has headers off (2). Left off, Power Query named the columns Column1 and Column2 (3). Tick My table has headers (4), then click OK (5).

Excel also turns the selected range into an Excel table with filter buttons, and it stays one after the query is finished. Our formulas that pointed at A2 and B2 kept working on the table.

In the Power Query Editor, check that both columns show the ABC icon, which means Text. Microsoft says you can only merge columns of a Text data type, and ours were Text already. Click the First column heading, then hold Shift and click Last, selecting them in the order you want, because Microsoft notes that the order of selection sets the order of the merged values.

Open the Transform tab and choose Merge Columns. In our window the text commands were folded into a Text Column button, so the route was Transform > Text Column > Merge Columns, while Microsoft's help names it Transform > Merge Columns.

Power Query Transform tab with Text Column open on Merge Columns, then the Separator list.
Click to expand
On the Transform tab (1), with First and Last selected (2), open Text Column (3) and choose Merge Columns (4). The Separator is --None-- by default (5), so choose Space (6) and click OK (7).

The Separator box starts at --None--. Its list offers Colon, Comma, Equals Sign, Semicolon, Space, Tab and --Custom--, and the new column is called Merged unless you type another name. We chose Space and clicked OK.

The preview showed one Merged column with Ava Stone, Leo Park and Mia Quinn, and First and Last were gone from the query. Choose Home > Close & Load, and Excel puts the result on a new sheet as a table, with the Queries & Connections pane reporting 3 rows loaded.

Power Query preview of one Merged column, then the loaded table on a new Excel sheet.
Click to expand
The query now has one Merged column (1) and a Merged Columns step (2). Close & Load puts it on a new sheet (3), and Queries & Connections reports 3 rows loaded (4).

The original sheet kept its First and Last columns. To keep them in the query output as well, Microsoft's steps use Add Column > Custom Column instead. Insert the first column, type an ampersand, a space in quotes and another ampersand, then insert the second column and select OK.

Microsoft's own tip leans that way, saying it's a good idea to create a new column and keep the original columns. Its reason is that this helps prevent refresh errors that might happen later.

Combine columns in Excel for the web

The same formulas work in a browser. In Excel for the web in Microsoft Edge, =A2&" "&B2 returned Ava Stone, =CONCAT(A3," ",B3) returned Leo Park and =TEXTJOIN(" ",TRUE,A2:B2) returned Ava Stone, with each formula shown in the formula bar.

Typing =CONC brought up CONCAT, described as "Concatenates a list or range of text strings", with CONCATENATE below it. Shift + F3 opened a web version of Insert function, with Next and Insert with preview buttons instead of OK.

Excel for the web suggesting CONCAT as you type, with CONCAT and TEXTJOIN results.
Click to expand
In Excel for the web, CONCAT is offered as you type (1), with CONCATENATE below it (2). =CONCAT(A3," ",B3) (3) returns Leo Park (4), and =TEXTJOIN(" ",TRUE,A2:B2) (5) returns Ava Stone (6).

In Excel for the web, read a formula in the formula bar. Microsoft says that there the formula bar shows the formula while the cells display the results, and it points to the desktop app for showing formulas in the cells.

Excel for Mac uses the same formulas. Microsoft's page on combining text lists Excel for Microsoft 365 for Mac, and its TEXTJOIN page says the function is available on Windows or Mac if you have Office 2019 or an Office 365 subscription.

Fix a joining formula that will not work

When a joining formula shows #NAME?, look for a missing quote first. We typed =CONCATENATE(A2,space,B2), with the word space instead of a space in quotes, and K2 showed #NAME?, with a warning icon beside it. Replacing space with " " gave Ava Stone.

If one formula shows as text, check whether the cell was formatted as Text before you entered it. Our N2 was set to Text before we typed =A2&" "&B2, and the cell displayed the formula itself. Switching the format back to General changed nothing on its own, but pressing F2 and then Enter made it calculate Ava Stone.

Excel #NAME? error from a missing quote, and a formula shown as text in a cell.
Click to expand
The word space typed without quotes (1) gives #NAME? (2). In a cell formatted as Text, setting the format back to General (3) still leaves the formula showing as text (4). After F2 and Enter, the cell shows Ava Stone (5).

If formulas across the sheet show as text, check whether Show Formulas is on. On our sheet Formulas > Show Formulas switched each formula cell to its formula, and selecting it again brought the results back. Microsoft gives Ctrl + ` as the shortcut, and our guide to Excel formulas showing as text covers the rarer causes.

On a protected sheet, a locked cell will not take the formula. We protected a sheet without unlocking any cells, and typing in the result cell raised the message "The cell or chart you're trying to change is on a protected sheet", while the formula worked after Review > Unprotect Sheet. Microsoft notes that cells others should edit can be unlocked before you protect the sheet. If the sheet has a password, our guide on how to unprotect an Excel worksheet covers that step.

For anything else, click the result cell and read the formula bar, which shows exactly which cells the formula joins. If the formula still looks right and the result is wrong, our broader guide can help you fix Excel formulas that are not working.

Why Merge Cells does not combine columns

Merge Cells looks like the obvious button, but it joins the cells, not their contents. When we selected R3:S3, holding North and Pier, and chose Merge Cells, Excel warned: "Merging cells only keeps the upper-left value and discards other values".

We clicked OK, and the merged cell showed only North. Unmerging it did not bring Pier back, so the value was gone rather than hidden. On the untouched pair in row 2, =R2&" "&S2 returned North Pier with both cells intact.

Excel merge warning over North and Pier, the lost value after unmerging, and North Pier.
Click to expand
With North and Pier in R3:S3 (1), Merge Cells shows a warning that only the upper-left value is kept (2). After unmerging, Pier is gone (3). A formula on the intact pair, =R2&" "&S2 (4), returns North Pier (5).

If you merge source cells by mistake, press Ctrl + Z straight away, which is Microsoft's shortcut for undoing a merge immediately. Later on you can still unmerge cells in Excel to get the grid back, but as our sheet showed, Unmerge Cells alone does not restore a discarded value. If you wanted the merged look for a heading, our guide on how to merge cells in Excel without losing data shows a way that keeps every cell.

How we tested this guide

We tested this in Excel for Microsoft 365 on Windows 11, version 25H2, joining a made-up list of first and last names with formulas, Flash Fill and Power Query.

Frequently Asked Questions

How do I combine two columns in Excel with a space?

With first names in A and last names in B, type =A2&" "&B2 in C2 and press Enter. Then select that cell and the cells below it and press Ctrl + D to fill the formula down.

How do I concatenate in Excel without extra spaces from blank cells?

Use =TEXTJOIN(" ",TRUE,A2:B2). TRUE tells Excel to skip empty cells, while FALSE kept an empty slot between the separators in our test.

What is the difference between CONCAT and CONCATENATE?

CONCAT is the newer function and also accepts whole ranges, while CONCATENATE stays in Excel for older workbooks. Neither adds a separator, so you type the space or comma yourself, and both returned Ava Stone on our sheet.

How do I keep the combined text after deleting the original columns?

Copy the combined cells and paste them with Home > Paste and the first icon under Paste Values before you delete anything. The pasted cells then hold plain text, so removing columns A and B no longer affects them.

Why does my formula show as text instead of the result?

Two common causes are Show Formulas being on, which affects the whole sheet, and a cell formatted as Text before you typed. For a Text cell, switch it to General and then press F2 and Enter, because changing the format alone did not fix it on our sheet.

Why did my percentage turn into a decimal when I joined it?

The & sign joins the stored value, so 40% became 0.4 in our test. Wrap the cell in TEXT, as in ="Rate: "&TEXT(H2,"0%"), to keep the percentage.

Does Merge Cells combine the text in both cells?

No. Excel keeps only the upper-left value and discards the rest, and unmerging did not bring the lost value back on our sheet, so press Ctrl + Z straight away if you merged by mistake.

Share