To keep a space between two pieces of text in Excel, include a space character as part of the formula: with a first name in A2 and a last name in B2, enter =A2&" "&B2. The quotation marks contain one ordinary space, so the result for Ada in A2 and Lovelace in B2 is “Ada Lovelace.” You can use the same idea with CONCAT or TEXTJOIN when combining more cells.
This works because Excel does not automatically add spaces when it joins text. The space must be supplied in the formula, either as a quoted text value or as the delimiter in a joining function. The instructions below are for Microsoft Excel for Microsoft 365 on Windows. The formulas also work in current Excel for Mac and Excel for the web; some menu labels or keyboard shortcuts can differ, but no special setup is required.
What You’ll Need
- An Excel workbook with the text you want to combine, such as first and last names in adjacent cells.
- A blank cell in which to show the combined result.
- A basic understanding of which cells contain each piece of text. The examples use A2 and B2, but you can substitute your own cell references.
Step-by-Step
- Identify the source cells and the desired gap. Decide what you are joining and where the space should go. If A2 contains “Ada” and B2 contains “Lovelace,” the desired result is “Ada Lovelace,” with one space between the two words. If you want a comma followed by a space, or a space around a dash, identify that separator before writing the formula. Excel will join exactly what you specify; it will not infer punctuation or spacing from the contents.
- Select the result cell. Click an empty cell where the combined text should appear, such as C2. Keeping the result in a separate column leaves the original data unchanged, which is useful if you later need to correct a name or change the separator. If C2 already contains information, choose another empty cell rather than overwriting it.
- Join two cells with the ampersand operator. With a first name in A2 and a last name in B2, enter
=A2&" "&B2in C2 and press Enter. The formula returns “Ada Lovelace” when A2 is “Ada” and B2 is “Lovelace.” The ampersand joins each part in order: the value from A2, a literal space inside quotation marks, then the value from B2. This is a straightforward option for two or a few pieces of text. - Use CONCAT if you prefer a function. With the first name in A2 and the last name in B2, enter
=CONCAT(A2," ",B2)in C2. It returns “Ada Lovelace” for those example values. The space is a separate argument between the two cell references. CONCAT is available in modern Excel, including Microsoft 365. If you need to share a workbook with someone using a very old Excel version, the ampersand method is a simpler compatibility choice. - Add separators between several cells. With a street number in A2, street name in B2, and apartment detail in C2, enter
=A2&" "&B2&" "&C2in D2. If the cells contain “24,” “Oak Street,” and “Unit 3,” the formula returns “24 Oak Street Unit 3.” Each quoted space is a separate separator, so you can omit one where it does not belong. For example, a city and state might need a comma and a space rather than only a space. - Use TEXTJOIN for a range or a repeated delimiter. With the words in A2 through A4, enter
=TEXTJOIN(" ",TRUE,A2:A4)in B2. If A2 is “Excel,” A3 is “keeps,” and A4 is “spaces,” the result is “Excel keeps spaces.” The first argument supplies one space as the delimiter, and TRUE tells Excel to ignore empty cells. TEXTJOIN is convenient when combining many cells because you do not have to type a separator between every cell reference. - Fill the formula down if you have multiple rows. If each row has a first name in column A and a last name in column B, put the formula in C2, then use the fill handle at the lower-right corner of C2 to drag it down. Excel adjusts the references by row, so the next row uses A3 and B3. Check a few results, especially rows with blank cells or names that already contain spaces. In Excel for Mac and Excel for the web, the fill handle works similarly; you can also copy the formula and paste it into the selected cells.
- Check the result before replacing or exporting data. Confirm that the intended separator appears once and that no word has been accidentally joined to the next one. A formula result updates when its source cells change. If you need a fixed text copy rather than a live formula, copy the result cells and use Paste Special > Values on Windows. On Mac, use Home > Paste > Paste Values or the Paste Special options available in your Excel version. Keep the original formula column until you have verified the pasted values.
Common Mistakes
Leaving out the space argument. A formula such as =A2&B2 joins the cell contents directly. With “Ada” and “Lovelace,” the result is “AdaLovelace.” Add &" "& between the references, or put a space delimiter in TEXTJOIN.
Typing a space outside quotation marks. In a formula, a blank between cell references does not create a text space. Put the intended character inside straight double quotation marks, as in " ". Curly quotation marks copied from formatted text can also cause a formula error, so replace them with ordinary straight quotation marks if Excel rejects the entry.
Putting the space in a cell but not referencing it. A cell containing a space is not included automatically. You would have to include that cell reference in the formula, and a literal quoted space is usually clearer. A cell-based separator can be useful if you want to change the separator in one place, but ensure the formula actually refers to that cell.
Adding separators around blank cells unintentionally. A formula that puts a space between every reference can leave extra spaces when one of the cells is empty. For instance, joining A2, B2, and C2 with fixed spaces may produce a leading or trailing gap if an end cell is blank. TEXTJOIN with TRUE can skip empty cells in a range. For more specific combinations, check the source data and decide how blank values should be handled.
Confusing a visible gap with a typed space. Some cells have leading or trailing spaces that are hard to see. Those spaces come from the source text, not from the joining formula, and they can make the output look uneven. Cleaning the source values may be necessary before concatenation.
Tips
- Choose the easiest method for the job. Use ampersands for a small number of pieces, CONCAT when you like a function-style formula, and TEXTJOIN when you need the same delimiter across a range. All three can preserve spaces when the separator is explicitly supplied.
- Use a delimiter that matches the text. A space works for names and ordinary word combinations. For city and region, a comma followed by a space can be clearer; for labels, a colon and space may be appropriate. Put the exact punctuation inside the quoted text argument.
- Keep source cells separate when possible. Combining text in a new column makes it easier to sort, edit, or reuse the original parts later. It also means you can change a separator without manually editing every combined result.
- Use cell references rather than retyping text. Referencing A2 and B2 means corrections to the original values flow through to the result. If you type names directly into the formula, those names will not update when the source data changes.
- Know what TRIM can and cannot do. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. With first and last names in A2 and B2, enter
=TRIM(A2&" "&B2)in C2. If A2 is “Ada ” with an accidental trailing space and B2 is “Lovelace,” it returns “Ada Lovelace.” TRIM is useful for ordinary spaces, but it does not remove every unusual whitespace character. - Check regional formula settings if needed. The examples use commas between function arguments, as requested for standard English-language Excel syntax. Some installations configured for other regional settings use semicolons in formulas. If Excel reports a problem with a function’s separators, check the list separator setting for that installation; the quoted space itself remains the same.
Troubleshooting
The result has no gap. Edit the formula and confirm it includes a quoted space between the cell references. In the formula bar, a correct two-cell pattern should look like =A2&" "&B2. Do not add the space by pressing the spacebar between A2 and B2 outside the quotation marks.
The result has two or more spaces. One may come from the formula and another from a source cell. Click each original cell and inspect its content in the formula bar. If the source has accidental extra ordinary spaces, use TRIM on the combined expression or clean the source values. If the gap is intentional, reduce the number of space characters in the formula to match the desired result.
There is a gap at the beginning or end. A leading or trailing space can come from a blank cell included in a fixed-separator formula, or from a source value that already begins or ends with a space. Check each contributing cell. If you are joining a range and want blank cells skipped, TEXTJOIN with its ignore-empty option set to TRUE is often the best fit.
Excel displays the formula instead of the result. Make sure the cell is not formatted as Text. Change its format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. Also check that the entry begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; the key is usually the grave-accent key. On Mac, use Control+` where that shortcut is available, or turn off Show Formulas from the Formulas tab.
The formula shows an error after you paste it. Replace smart or curly quotation marks with straight double quotation marks, and check that every opening parenthesis has a matching closing parenthesis. For function formulas, confirm that your Excel uses the argument separator in the formula. If the source cells contain errors, joining them can carry an error into the result; correct the source error first.
A strange gap remains even after trimming. The character may be a nonbreaking space or another imported whitespace character rather than a standard space. This can happen with text copied from websites or exported reports. Try cleaning the source data or replacing the unusual character in the source before joining. TRIM alone may not remove it, so do not assume the formula separator is the cause.
Frequently Asked Questions
Why does Excel remove the space when I concatenate cells?
Excel does not add a separator automatically. A concatenation formula combines only the values and text you specify. Include a literal space, such as " ", between the cell references, or set the delimiter in TEXTJOIN.
Is CONCATENATE different from CONCAT for spaces?
No. Both functions need you to provide the space as an argument. CONCAT is the newer function in current Excel, while CONCATENATE remains available for compatibility. The ampersand operator also works and is often the shortest way to join two cells.
Can I put more than one space between the joined values?
Yes. Put the exact number of spaces inside the quoted text separator. Be aware that repeated spaces can be hard to distinguish on screen and may be collapsed by other systems when copied or published, so use them only when your output specifically requires them.
How can I join cells while skipping blanks?
Use TEXTJOIN and set its ignore-empty argument to TRUE. Supply the desired delimiter as the first argument and the cells or range to combine after the ignore-empty setting. This avoids adding a separator for cells Excel considers empty.
Will the spacing update if I change a source cell?
Yes, as long as the combined result is still a formula that refers to the source cell. Excel recalculates the result when the source value changes. If you paste the result as a value, it becomes fixed text and will not update automatically.
Do the same formulas work in Excel for Mac or the web?
Yes. The ampersand, CONCAT, and TEXTJOIN formulas shown here work in current Excel for Mac and Excel for the web. The ribbon layout and some keyboard shortcuts can differ, but the space must still be included explicitly in the formula.
Related
- Import Excel into a SharePoint List: Step-by-Step
- How to Highlight a Row in Excel with a Keyboard Shortcut
- How to Import an Excel Document Into Microsoft Word
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets for Scheduling: Setup, Tips and LimitsYes. Google Sheets can work well for a straightforward personal, shift, project, or room schedule: put dates, times,…
- How to Remove a Section Break in Word Without Losing TextTo remove a section break in Word, show formatting marks, locate the line labeled Section Break, then place…
- How to Set Up an Automatic Forwarding Rule in OutlookTo automatically forward selected Outlook messages, create a rule that matches a condition—such as a sender or subject—and…
- Outlook 2013 Rules by Sender: Setup and FixesYes. In Outlook 2013 for Windows, select a message from the sender, then choose Home > Rules >…
- How to Insert a New Row in Excel on Windows and MacTo insert a new row in Excel for Microsoft 365 on Windows, select the row heading where you…
- How to Extend a Dropdown in Google Sheets: Full GuideTo extend a dropdown in Google Sheets, edit its data validation rule: add choices to the rule or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.