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 (-).

dataset to replace space with dash in google sheets

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

navigating to find and replace in from the edit tab

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.

finding all cells with spaces using find and replace animated

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.

setting conditions for replace

Our result:

replace space with dash in google sheets using find and replace

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.

choosing the find and replace range

  1. 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

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

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:

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

the substitute function syntax

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

=SUBSTITUTE(B3," ","-")

using substitute function to replace space with dash in google sheets

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.

Replacing space with underscore and comma in Google Sheets with Substitute formula

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

Concatenate and Substitute Values

Have a look at the following dataset:

dataset with multiple values over multiple cells

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 function can't replace existing space with dash

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:

=SUBSTITUTE(JOIN(" ",B3:D3)," ","-")

using substitute and join to replace space with dash in google sheets

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:

using textjoin and substitute to replace space with dash in google sheets

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:


trimming extra whitespace using the trim function in substitute

  1. 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

navigating to trim whitespace from toolbar

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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply