Google Sheets is a powerful cloud-based spreadsheet software that is offered as part of Google’s Drive suite of apps. If you are new to Sheets, you may need to learn how to perform some basic tasks in the app. Thankfully, removing unnecessary spaces from your data is super easy.
There are a few ways you can go about removing extra spaces, including the trim and substitute functions, the built-in Find and Replace tool, and a popular add-on for Sheets called Power Tools. In this guide, we will walk you through how to use each of these methods.
Let’s start with the Trim function. This function works by removing leading, trailing, and repeating spaces in a text. To use it, select the cell that contains the data you want to remove spaces from, then enter the formula “=trim(cell)” in the adjacent cell. The new cell will display the same values without the leading and trailing spaces, with just a single space between the numbers.
The Substitute function is a bit more complex, but it is just as effective. The full equation is in the description below, along with the option occurrence number command, which works by replacing all occurrences of your search for variable by default. To use this function, select the cell containing the data you want to remove spaces from, then enter the formula “=substitute(cell,” “,””)” in the adjacent cell. This will remove all spaces from the selected cell, and the new cell will display the exact same values with all the spaces removed.
If you need to remove spaces from multiple cells, you can use the small square at the bottom right corner of the cell containing the substitute or trim function. Click and hold down your mouse button, then drag the cursor over the cells you need to copy the function to.
Google Sheets also has a built-in Find and Replace tool that can remove spaces directly from existing text. Select the cell that contains the data you want to remove spaces from, then press Ctrl or Command Shift + H to open the tool. Enter one single space in the Find box, then click the Replace All button and select “Done”. This will remove all spaces, though you may need to adjust your realignment in some cells to find and remove additional excess spaces. If there are multiple spaces, you need to remove, enter two spaces in the Find box instead.
Finally, you can use the Power Tools add-on if you want to remove spaces from larger groups of cells. If you don’t already have Power Tools added to Google Sheets, make sure to install it through the add-ons menu. Then, select Add-ons from the menu bar and select Power Tools to open the tool. Click “Start,” then select “Text,” and click “Remove.” Select “Remove all spaces” from the “Remove spaces in delimiters” drop-down menu, then click the “Remove” button.
In conclusion, removing unnecessary spaces from your data in Google Sheets can be done in a variety of ways. Whether you prefer the Trim or Substitute function, the Find and Replace tool, or the Power Tools add-on, you can choose the method that works best for you.