3 Ways to Replace Spaces Between Characters in Excel Text Strings
by Avantix Learning Team | Updated October 20, 2021
Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows)
In Excel, you can replace spaces in text strings with underscores, dashes or other values using Find and Replace, by creating a formula or by using Flash Fill.
Recommended article: How to Delete Blank Rows in Excel (5 Fast Ways to Remove Empty Rows)
Do you want to learn more about Excel? Check out our virtual classroom or in-person Excel courses >
1. Replacing spaces using Find and Replace
To replace spaces using Find and Replace:
- Select the range of cells containing text strings that include spaces you want to replace.
- Press Ctrl + H to display the Find and Replace dialog box. You can also click the Home tab in the Ribbon and select Replace in the Find & Select group.
- In the Find what box, type a space.
- In the Replace with box, type an underscore, dash, or other value. If you want to replace the space with nothing, leave the box blank.
- Click Replace All. A dialog box appears indicating the number of replacements.
- Click OK.
Below is the Find and Replace dialog box in Excel to replace spaces with underscores:
2. Replacing spaces using a formula
You can also create a formula and use the SUBSTITUTE function to replace spaces in text strings.
The Excel syntax for the SUBSTITUTE function is:
=SUBSTITUTE(text, old_text, new_text, [instance_number])
The text is typically a reference to a cell. The old text is the text you want to replace. The new text is the text you use as a replacement. Instance number is optional.
The simple syntax of the SUBSTITUTE function is:
=SUBSTITUTE(cell containing the data you want to replace, the value you want to remove, the value you want to use as a replacement, instance if required)
Text must appear in quotation marks or double quotes.
For example, if you want to replace spaces with underscores in cell B1 based on a text string in A1, the formula would be:
=SUBSTITUTE(A1," ","_")
If you need to remove leading and trailing spaces, you can combine the SUBTITUTE function with the TRIM function as follows:
=SUBSTITUTE(TRIM(A1)," ","_")
To convert the formula to text, simply copy and paste the formula as values.
3. Replacing spaces using Flash Fill
If you have Excel 2013 or later, you can also try using Flash Fill to replace spaces. Because Flash Fill needs to see a pattern to the left, you will likely need to enter several examples before running Flash Fill.
To change spaces to underscores or other values:
- Click in a cell to the right of the cell with the spaces you want to replace.
- Enter the same entry as the original cell with underscores instead of spaces and press Enter.
- Repeat for the next 3 or 4 cells below the first cell.
- In the cell below the last entry, press Ctrl + E to run Flash Fill.
Be sure to check the entries generated by Flash Fill.
Subscribe to get more articles like this one
Did you find this article helpful? If you would like to receive new articles, JOIN our email list.
More resources
How to Merge Cells in Excel (with Shortcuts)
How to Use Flash Fill in Excel (4 Ways with Shortcuts)
3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells
How to Replace Blank Cells in Excel with a Value from the Cell Above
10 Excel Flash Fill Examples (Extract, Combine, Clean and Format Data with Flash Fill)
Related courses
Microsoft Excel: Intermediate / Advanced
Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Introduction to Visual Basic for Applications (VBA)
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2023 Avantix® Learning
You may also like
Excel XLOOKUP Function (Overview, Syntax + Formula Examples)
The XLOOKUP function is a replacement for Excel's traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). It has a new set of arguments and is available in Excel 2021 and 365. It allows you to look up a value from an array in a range or table and return one or more results. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data set and return a range.
How to Use Go to Special in Excel to Find, Select, Replace and Format Data
You can use Go To Special in Excel to quickly find and select cells of a specific type within your worksheet. It only selects cells in the current worksheet, not the entire workbook. Go To Special also searches within the selected range if a range has been selected. You can find many useful things using the Go To Special dialog box including formulas, constants, blanks, visible cells and conditional formatting. After you select multiple cells with Go To Special, you can enter data in the selected cells by pressing Ctrl + Enter or by applying formatting.
How to Lock Cells in Excel (3 Ways)
It's easy to lock and protect cells in Microsoft Excel to prevent users from changing data or formulas. This involves a two-step process …
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca
