Table of Contents
Replace Space with Underscore in Excel is an important feature that helps users to efficiently and effectively organize and store their data. This feature allows users to replace all spaces in a cell with an underscore or any other character. This is useful for organizing data into a consistent format that can be easily read and understood. By using this feature, users can avoid creating long and confusing strings of data that would otherwise be difficult to interpret. Furthermore, this feature can be used to help create more meaningful reports and analyses. This feature also helps to make data more concise and easier to search and analyze. Overall, Replace Space with Underscore in Excel is an invaluable tool for improving the efficiency and accuracy of data analysis.
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, " ", "_")
Column B returns each string in column A with each of the spaces replaced by underscores.