How can I replace spaces with underscores in Excel?

By /

This is a guide on how to replace spaces with underscores in Excel. Spaces are often used to separate words or data in Excel cells, but in some cases, it may be necessary to replace them with underscores for better organization or formatting. By following these steps, users can easily replace all spaces in a selected range of cells with underscores to achieve their desired result. This process can be useful for data manipulation, creating consistent file names, or formatting for aesthetic purposes.

Replace Space with Underscore in Excel

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:

Excel replace space with underscore using Find and Replace

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, " ", "_")

Excel replace space with underscore using SUBSTITUTE function

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:

x

Related terms:

  1. How can Power BI replace blank spaces with text?
  2. PySpark: Replace Zero with Null How to Replace Zero with Null in PySpark?
  3. How to remove leading and trailing spaces in excel using vba
  4. How can I count the number of spaces in a cell using Excel?
  5. How can I remove spaces from column names in PySpark?
  6. Systematic Pedestrian and Cycling Environmental Scan (SPACES) Instrument
  7. How would you write a macro to remove all spaces from a string?
  8. How do you import a CSV file into R with column names that contain spaces?
  9. What is the simplest way to remove spaces from column names in a PySpark dataframe?
  10. How can I use Find and Replace with Wildcards in Excel?