How to Remove Spaces Between Words in Google Sheets

Excess spaces in data can make it difficult to read and analyze, but Google Sheets can easily remove them. This article will show you how to use simple functions to quickly and easily remove spaces between words in google sheets, helping you to better organize and analyze your data.


A Sample of Practice Spreadsheet

You can copy the spreadsheet that we’ve used to prepare this article.


2 Simple Scenarios to Remove Spaces Between Words in Google Sheets

In a Google Sheets dataset, it is often seen that if we remove spaces between words, it can enhance clarity and uniformity, and some data may not require the spaces given to them. You may wish to remove all spaces between words to simplify a large dataset or remove extra spaces to standardize data entered by different people.

1. Remove All Spaces

The following two solutions can be used to eliminate every space from a spreadsheet. In terms of performance, they are quite easy and effective. We will use the following dataset to demonstrate the process.

data to remove spaces between words in google sheets

I. Using Find & Replace Tool

The procedures to utilize the search and replace tool to eliminate all spaces in Google Sheets are listed below.

using find and replace tool to remove spaces in google sheets

Steps:

  • First, we must choose the cells from which we wish to delete the space. The cells in this instance are B5:C9.

selection of cell while using find and replace tool

  • Go to the “Edit” tab of the toolbar and select the “Find and Replace” option.

location of find and replace tool

  • Alternatively, we can use the keyboard shortcut CTRL+H (for Windows) or CMD+H (for Mac).
  • We will have the following interface of the Find and replace dialogue box.

find and replace dialogue box in google sheets

  • Now in the Find box, we will use a single whitespace (” “).
  • Leave the “Replace with” field empty.
  • Then, Click the “Replace all” button to remove all spaces between words in the selected cells.

how to use find and replace tool to remove spaces in google sheets between words

  • It will display the notification that follows, summarizing all the changes that have been done.

notification inside find and replace dialogue box

  • To conclude the process click Done.

final output using find and replace tool


II. Applying SUBSTITUTE Function

The SUBSTITUTE function in Google Sheets allows you to replace a specific substring in a cell with a new substring. Simply input the cell reference, the text to be replaced, and the replacement text to use the function. The SUBSTITUTE function will replace all instances of the text specified in the first parameter with the text specified in the second parameter within the selected cell

substitute function syntax

Using the SUBSTITUTE function, we can also eliminate all the spaces from a spreadsheet. Check the following procedure.

Steps:

  • First, we’ll create a new heading in column E.
  • For example, we will use the column name Remove all space in cell E4.

applying substitute function

  • Next, we will choose cell E5.
  • To eliminate all spaces from the phone number, we will apply the formula below.
=SUBSTITUTE(C5," ","")

applying substitute function to remove spaces

  • Here, “ “ denotes single whitespace which will be replaced by not blank “”.
  • Finally, press ENTER to view the result.

how to remove all spaces between words in google sheets using substitute function

The aforementioned image makes clear that there are no gaps in the numbers. All were taken out and replaced with not blank(“”).


Similar Readings


III. Utilising REGEXREPLACE Function

In Google Sheets, you may use the REGEXREPLACE function to replace text that matches a regular expression with a new value.

When working with huge datasets that could contain inconsistent or improperly formed text values, it is a powerful tool for complex text manipulation tasks.

Making sure that the data is arranged correctly and is clean, can help to save time and effort when processing or analyzing the data.

regexreplace function syntax

To get rid of extra spaces, we can also utilize the REGEXREPLACE function. We’ll use the dataset below as an example.

data for removing spaces in google sheets

Steps:

  • Once again, we’ll make a new header containing the Name and Phone Number in cells B11 and C11.
  • Next, we’ll choose cell B12.
  • We can employ the REGEXREPLACE function in one of two ways. One involves the regular expression, and the other involves the normal formula. Since both formulas provide the same result, we shall demonstrate both.
  • To eliminate spaces from a cell, we typically use the formula below.
=REGEXREPLACE(B5," ","")
  • Press ENTER to see the result.
  • We may use the fill handle to apply the formula to the following cell range, from B13 to B16.

normal usage of regexreplace function in google sheets

NOTE: The REGEXREPLACE function works similarly to SUBSTITUTE Function.
  • We will apply the following formula in cell C12 to demonstrate the use of the regular expression.
=REGEXREPLACE(C5,"\s","")
  • The “\s” signifies whitespace in regular expressions.
  • Press ENTER once more to see the gap filled in.
  • Once again, we can observe the change by using the fill handle in the relevant cells.

using regular expression of regexreplace function to remove spaces between words in google sheets

To eliminate excess spaces that are either leading or trailing, we have attempted to illustrate both uses of the REGEXREPLACE function. Even double gaps may exist in some cells.

Read More: Use REGEXEXTRACT Function Between Two Characters in Google Sheets


2. Remove Extra Spaces Only (Leading, Trailing, or Double Spaces)

While collaborating, we frequently come across incomplete datasets or datasets that are disorganized, such as having leading, trailing, or double gaps between words or numbers.

This frequently causes issues with data analysis. So, to perform an analysis, we must eliminate the spaces. To resolve the issue, we can employ the following strategies.

Assume we have a simple dataset of people with their names and addresses. We will use this simple dataset to demonstrate how to eliminate the spaces in the spreadsheet.

data from removing trailing, leading and spaces between words in google sheets

I. Utilization of Trim Whitespace Option

To directly remove leading and trailing spaces between words in the google sheet dataset, we can also use the Trim Whitespace option. To demonstrate the procedure, we shall yet again make use of the previous data set.

Steps:

  • At first, we need to select the cells that we need to remove leading and trailing whitespaces. For this example, the cell range is B5:C9.
  • Data > Data Clean-up > Trim Whitespace

how to navigate trim whitespace in google sheets

  • Choose “Trim whitespace” from the navigation pane after using right-clicking on the chosen cells.
  • When the following notification is presented, The chosen cells will no longer have any leading or trailing whitespace.

notification after using trim whitespace

  • Click OK to see the result.

 remove all space between words in google sheets using trim whitespace

We anticipate that this detailed procedure and example dataset will help show how to use the Trim Whitespace feature in Google Sheets.


Similar Readings


II. Application of TRIM Function

The TRIM function performs like a fine wine. It eliminates any excess space inside a cell. The TRIM function removes leading and trailing spaces from a text string in Google Sheets. It is useful for cleaning up data and ensuring text values are consistent.

syntax of trim function

Steps:

  • In cells B11 and C11 directly below the table, we will first add two new headings. We will use the Name and Address for this example once more.
  • After that, we’ll choose cell C12. We will show this example for column C only.
  • The formula we’ll apply is as follows:
=TRIM(C5)

use of trim function to remove space

  • To see the update at the end, press ENTER.
  • The output will be as follows once we apply the formula to the entire table.

use of trim function to remove spaces between words in google sheets

The differences between the two tables are very obvious. The TRIM function removed all extra spaces within the cells.


III. Implementation of Data Clean-up Tool

We can also use the built-in feature of google sheets, the Data clean-up tool. It generally cleanses duplicate records and unnecessary space from spreadsheets while also cleaning, adjusting, and standardizing the data.

To demonstrate how to utilize the data clean-up tool to remove excess spaces from a spreadsheet, we will use the above-mentioned dataset.

Steps:

  • Navigate the Data clean-up tool from the Data tab located in the toolbar.
  • Following that, select the Clean-up suggestions option.

where to find data clean-up tool

  • Under the headline “Clean-up suggestions,” a dialogue box will appear.
  • Here, we can notice that it is already highlighting extra spaces and is awaiting the command to remove them or not.
  • Either choose the Trim all option or click the “” mark to eliminate all of the suggested additional space. Trim all operates on a column-by-column basis.

data clean up suggestions box

  • The box will then display the subsequent status.

final notification after data clean-up in google sheets

We can eliminate empty spaces more quickly than any other technique with the aid of a data cleanup tool. It is quite practical and suitable for cleaning large datasets.

output after using data clean up to remove spaces between words in google sheets

Read More: Generate Random Numbers or Text Between Limits in Google Sheets


Conclusion

We have shown several ways to remove extra spaces between words in Google Sheets, including using TRIM, SUBSTITUTE, REGEXREPLACE, and the Find and replace tool. We hope our examples and explanations have helped remove spaces from your datasets. If you need further assistance, don’t hesitate to ask. If you need any help with Google Sheets, visit OfficeWheel.


Related Articles

Ishrak Khan

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo