There are two common ways to replace spaces with underscores in cells in Excel:
Method 1: Use Find and Replace Feature
Method 2: Use SUBSTITUTE Function
The following examples show how to use each method in practice with the following list of basketball positions in Excel:
Let’s jump in!
Example 1: Replace Space with Underscore Using Find and Replace Feature
First, highlight the cell range A2:A11.
Then type Ctrl + H to bring up the Find and Replace feature in Excel.
Then type a single space in the Find what box and type a single underscore in the Replace with box.
Once you click Replace All, the spaces in each cell will be replaced with underscores and a message box will appear that tells you how many replacements were made:
Notice each space in each cell has been replaced with an underscore.
Example 2: Replace Space with Underscore Using SUBSTITUTE Function
Another way to replace spaces with underscores in Excel is to use the SUBSTITUTE function.
We can type the following formula into cell B2 to replace each space in cell A2 with an underscore:
=SUBSTITUTE(A2, " ", "_")
We can then click and drag this formula down to each remaining cell in column B:
Column B returns each string in column A with each of the spaces replaced by underscores.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Add Single Quotes to Text
Excel: How to Extract Text Before a Character
Excel: How to Extract Text After a Character
Excel: How to Remove Specific Text from Cells
