The other day someone in my Excel course asked if there was a way to quickly replace all of the spaces in a group of cells with dashes or underscore characters.
One of Excel’s hidden strengths is its ability to manipulate data.
There are 3 ways to replace spaces with a dash or underscore character. Read on to become a master manipulator… of data in Microsoft Excel!
If, instead of replacing spaces with a dash or underscore, you’d like to remove them altogether, check out this article on how to remove spaces from words or numbers in Excel.
Replace Spaces with Find and Replace
Using Find and Replace is one way to selectively update information in an Excel worksheet.
Here is what you need to do.
- Select the range of cells that you would like to update.
- Press Ctrl + H to display the Find and Replace dialog box or click the Find & Select button in the Home > Editing group, then select Replace.
- Type a space in the Find what: box then press Tab to move down one field.
- Type an underscore character (_) in the Replace with: box.
- Click Replace All.
Replace Spaces Using the SUBSTITUTE Function
The SUBSTITUTE function can be used to replace one string of characters with another. In this example, we’ll replace the spaces between words with an underscore character.
Before we get started, let’s look at how the SUBSTITUTE function works.
SUBSTITUTE Function Syntax
Here is the syntax of the SUBSTITUTE function:
=SUBSTITUTE(Text,Old_text,New_text,[Instance_num])
- Text is the cell address containing the text you want to update.
- Old_text is the existing character (a space).
- New_text is the revised text (an underscore).
- Instance_num identifies which instance of Old_text should be updated. If this parameter is omitted, all instances of Old_text are updated.
In the example, below, the SUBSTITUTE function was used to replace spaces with underscore characters. Cell E8 is highlighted for your reference.
Replace Spaces Using Flash Fill
Flash Fill has been included with every new version of Excel since its release with Excel 2013. If you’ve never used this feature, you’re going to love it!
- Click to the right of an existing cell with text containing spaces.
- Carefully type the cell’s content with an underscore in place of any spaces you’d like to remove, then press Enter.
- Type the first letter of the text in the next row then press Enter to let Flash Fill complete the remaining entries.
Work Smarter, Not Harder!
I hope these 3 techniques for replacing spaces with a dash or underscore have saved you a little work. In my experience, any time spent learning more efficient ways to complete common tasks in Microsoft Excel will pay off tenfold!
If you’ve enjoyed this article, there are plenty of other shortcuts you can learn about in Excel!
