Replace Space with Dash in Google Sheets (2 Ways)

Today we have a look at a couple of different ways by which we can replace space with a dash in Google Sheets. One method talks about direct replacement, and another discusses substitution in another cell entirely.

Let’s get started.

Replace Space with Dash in Google Sheets

1. Find and Replace: Replace Space with Dash in the Same Cell or Column

We first look at how we can replace spaces within the same cell in a column. The easiest way to do that is by using the Find and Replace option of Google Sheets.

For our example, let’s say we want to replace all the spaces between the text in the following cells with a dash or hyphen (-).

Step 1: Navigate to Find and Replace from the Edit tab (Edit > Find and Replace). Keyboard shortcut: CTRL+H

Step 2: In the Find field of the Find and replace window, type in a single whitespace. Clicking on the Find button will cycle you through all the cells containing blank spaces in the worksheet.

This proves that Google Sheets recognizes the spaces that are in these cells, making it easy to replace them automatically.

Step 3: Input a dash (-) in the Replace with field and click the Replace button to replace the spaces with it. Continue doing so until all spaces are replaced with a dash or till you are satisfied.

Our result:

Advantages of Find and Replace in this scenario

1. The biggest advantage of this method is that you can use Replace All to automatically replace any value over a given in Google Sheets in a single click.
2. You get to select the effect range of Find and Replace. It can be the entire spreadsheet, the current sheet only, or a specific range of cells.

3. You can replace spaces with not only hyphens (-), but also underscores (_), commas (,) or any combination of delimiters you can think of.

Replacing spaces with commas and whitespace over a fixed range

For a more in-depth guide to Find and Replace over multiple scenarios, please see our Find and Replace in Google Sheets article.

Read More: How to Use Find and Replace in Column in Google Sheets

Similar Readings

• How to Find Hidden Rows in Google Sheets (2 Simple Ways)
• Find Frequency in Google Sheets (2 Easy Methods)
• How to Find Median in Google Sheets (2 Easy Ways)
• Find Largest Value in Column in Google Sheets (7 Ways)
• How to Find Edit History in Google Sheets (4 Simple Ways)

2. Replace Space with Dash in Google Sheets in a Different Cell

Using the SUBSTITUTE Function

We can also replace or substitute a space with a dash in Google Sheets by using the aptly named SUBSTITUTE function.

The SUBSTITUTE function syntax:

Let’s see the function in action first to understand it better:

Formula Breakdown

• Our text_to_search is a cell reference to cell B3.
• We are searching for whitespaces, therefore we input a space (“ “) in the search_for field.
• Our replace_with value will be a dash “-“.

While the function is simple to implement, it has certain limitations. That being its non-customizability. On its own at least.

We can only select one type of value to substitute and substitute with.

We also cannot control how many of the values will be replaced in the selected cell.

On the plus side, however, there is no limit to what we can substitute. Be it symbols, text, or numbers, the SUBSTITUTE function can replace anything with anything.

Read More: How to Search in Google Spreadsheet (5 Easy Ways)

Concatenate and Substitute Values

Have a look at the following dataset:

We have multiple values in different cells that we want to bring together into one cell and separate the text with a dash. Can we perform this with SUBSTITUTE?

No. A limitation that many users of SUBSTITUTE might face is its inability to work with text from multiple cells. In other words, the function cannot concatenate values.

What about the JOIN function?

While the JOIN function is meant to concatenate values from multiple cells with a delimiter, it does not replace existing delimiters with new ones.

JOIN cannot replace existing space with a dash

To remedy this issue, we can apply SUBSTITUTE over the result generated by JOIN to replace existing spaces with a dash.

Or better yet, combine the two functions together to give a result in a single cell.

Our formula:

Formula Breakdown:

• We have concatenated all the values over a range of cells, B3:D3, using the JOIN function.
• A delimiter of a single whitespace was applied to the JOIN function.
• The whitespace delimiter of the JOIN function was replaced by a dash or hyphen (-) using the SUBSTITUTE function. This also replaces any gaps that might be there in the cell.

Tip: Using the TEXTJOIN Function

You can use the TEXTJOIN function in place of JOIN in the formula. The advantage of TEXTJOIN is that you can set it to ignore empty cells (ignore_empty_field) if they are available in the cell range.

Results of the two functions:

With TEXTJOIN no extra whitespace or other delimiters are added to be replaced afterward.

To conclude, JOIN is best used when there are no empty fields in your dataset, and TEXTJOIN is used otherwise.

Read More: Find Value in a Range in Google Sheets (3 Easy Ways)

Extra Tip: Trimming Excess Whitespaces

Extra whitespaces can be a nuisance not only for spreadsheet presentation but also to work with text functions like SUBSTITUTE.

We can solve this in two ways:

1. Using the TRIM function inside SUBSTITUTE to remove extra whitespace.

We simply apply the TRIM function within the text_to_search field of the SUBSTITUTE Function:

2. Use Trim Whitespace Option from the Toolbar.

Simply select the range of cells or the whole spreadsheet (CTRL+A) and navigate to the Trim whitespace option:

Data > Data cleanup > Trim whitespace

Final Words

That concludes all the ways we can use to replace space with, but not limited to, dash in Google Sheets. While the Find and Replace may be the go-to option for a quick substitution, the SUBSTITUTE function can be combined with other functions to give a customized result.

Feel free to leave any queries or questions you might have in the comments section below.

Related Articles for Reading

• How to Find and Delete in Google Sheets (An Easy Guide)
• Use FIND Function in Google Sheets (5 Useful Examples)
• How to Find P-Value in Google Sheets (With Quick Steps)
• Find and Replace with Wildcard in Google Sheets
• How to Find the Range in Google Sheets (with Quick Steps)
• Find All Cells With Value in Google Sheets (An Easy Guide)
• How to Find Correlation Coefficient in Google Sheets
• Find Uncertainty of Slope in Google Sheets (3 Quick Steps)