If you work in Excel, I’ll bet that you deal with imperfect data. That means that you’ll need to clean up data and take actions like remove spaces in Excel workbooks. Let’s learn how.
Remove Spaces in Excel
Let’s say that you get a workbook with some ugly sentences. No offenses intended to imperfect sentences – it’s just that some have too many spaces!
The example below is all too common. A lot of data comes out of systems that don’t handle sentences and paragraphs properly, adding too much space and cruft in between.
To correct the issue, use the =TRIM(). function. In our example, we’re going to use =TRIM(B3) to trim the text inside the cell B3.
Check out the results below – Excel automatically cleans up the text and removes the double spaces inside the cell.
Don’t spend time rewriting and retyping your spaced sentences. Instead, just use the TRIM function and point it to a cell with overly spaced text.
Removing Line Break Spaces in Excel
You’ve already learned how to remove extra spaces between words. But many times, your data will have extra spaces either before or after the content in the form of line breaks. If you’re working with longer blocks of text, this is a must to clean up.
In this case, we’re going to use the =CLEAN() function. Applying this to a cell with line breaks will consolidate the text to a single line. In the example below, I use =CLEAN(B3) to remove the line break in the text from cell B3.
After you apply the text, Excel puts everything on a single line in the resulting cell.
Always remember: you can Copy your trimmed text and Paste as Values to paste out the cleaned-up text. That way, you don’t have to keep the original, overly spaced text.
Goal Seek in Excel