Ishrak Khan
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.
I. Using Find & Replace Tool
The procedures to utilize the search and replace tool to eliminate all spaces in Google Sheets are listed below.
Steps:
- First, we must choose the cells from which we wish to delete the space. The cells in this instance are B5:C9.
- Go to the “Edit” tab of the toolbar and select the “Find and Replace” option.
- 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.
- 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.
- It will display the notification that follows, summarizing all the changes that have been done.
- To conclude the process click Done.
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
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.
- Next, we will choose cell E5.
- To eliminate all spaces from the phone number, we will apply the formula below.
=SUBSTITUTE(C5," ","")
- Here, “ “ denotes single whitespace which will be replaced by not blank “”.
- Finally, press ENTER to view the result.
The aforementioned image makes clear that there are no gaps in the numbers. All were taken out and replaced with not blank(“”).
Similar Readings
- Difference Between COUNT and COUNTA in Google Sheets
- How to Move Between Tabs in Google Sheets (3 Easy Ways)
- Google Sheets Count Cells Between Two Numbers with COUNTIF Function
- How to Use IF Condition Between Two Numbers in Google Sheets
- How to Link Cells Between Tabs in Google Sheets (2 Examples)
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.
To get rid of extra spaces, we can also utilize the REGEXREPLACE function. We’ll use the dataset below as an example.
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.
- 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.
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.
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
- 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.
- Click OK to see the result.
We anticipate that this detailed procedure and example dataset will help show how to use the Trim Whitespace feature in Google Sheets.
Similar Readings
- Calculate Percentage Difference Between Two Numbers in Google Sheets
- How to Find Correlation Between Two Columns in Google Sheets
- Insert Rows Between Other Rows in Google Sheets (4 Easy Ways)
- Find Difference Between Two Columns in Calculated Field of Google Sheets Pivot Table
- How to Calculate Time Between Dates in Google Sheets (6 Ways)
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.
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)
- To see the update at the end, press ENTER.
- The output will be as follows once we apply the formula to the entire table.
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.
- 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.
- The box will then display the subsequent status.
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.
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
- How to Calculate Hours Between Two Times in Google Sheets
- Find Number of Months Between Two Dates in Google Sheets
- How to Find Missing Values Between Two Columns in Google Sheets
- Conditional Formatting Between Two Values in Google Sheets
- How to SUMIF Between Two Dates in Google Sheets (3 Ways)
- Insert Lines Between Cells in Google Sheets
- How to Find Unique Values Between 2 Columns in Google Sheets
