Whether it's the result of getting data
from online or typing errors,
there are many reasons why cells in Excel can end up
with extra spaces in them.
And these can make the sheet look messy and keep you
from correctly using some of Excel's tools and features.
So let's go over three ways
that you can clean up extra spaces in Excel.
In the first example,
we have a list of email addresses
with extra spaces both before and after the address.
The TRIM function is perfect for this.
Its job is to remove any spaces
from the beginning and end of a cell.
So we can type =TRIM and reference
the cell that we want to clean up and press Enter.
And then you can either use copy
and paste to copy it down
or double-click on this little bitty square here
to auto fill to the other cells.
Or another thing you can do when you have a list
of cells like this that you want to apply TRIM to,
is reference the entire range that you want
to apply the formula to and then press Enter.
Just keep in mind that doing this will group
the results. And that's what
this blue line symbolizes around all of our results.
This means that they can't be edited
or deleted without causing an error
to the other results in the group. So just keep this in mind.
But for this example,
I'm not too worried because I'm actually going
to copy the results,
and then paste them as values over
the originals. And then I'm going to delete these.
In this next example,
we again have a list with spaces before and after the words,
but each also contains a paragraph break - or a line break -
which we also need to get rid of.
And we can tell these have a line break
by testing them with the Wrap Text option.
So text will only wrap if either:
1. the cell is not big enough to fit all the contents
or 2. the contents contain a line break.
So if our column is big enough to fit all the words,
which it is, and we click on Wrap Text,
and our words still wrap,
then it means that they contain a line break.
So to clean this up,
we're going to use the TRIM function again,
but this time we're going
to combine it with the CLEAN function.
So CLEAN, removes 32 different types
of invisible, non-printable characters from cells.
And the most common of these is probably
the line break, like we have here.
But you can also get some of these non-printable characters
if your data has any weird coding.
Like if you copied it from a website.
So to clean up both the extra spaces and the line breaks,
we'll type TRIM,
then in parentheses, CLEAN,
and then add another parentheses, then select either your cell
or, like I'm doing here,
the entire range,
and close your two parentheses, and press Enter.
And, like before, our results are grouped.
But, again, I'm just going to copy them
and paste them over the originals as values,
delete these and, now, notice the extra spaces are gone.
And, if I click on Wrap Text, nothing happens.
So our line breaks are also gone.
The third example has longer text
and paragraph formatting that we want to keep.
So, here, what I actually want
to fix is the use of spaces after the period.
So it's now the norm
to type only one space after the final punctuation.
But a lot of people were taught
and got used to always typing two spaces
at the end of each sentence.
And also a couple of words
have too many spaces between them.
So to fix this, I'm going
to use Find and Replace. Pressing Ctrl + H on my keyboard,
I'm going to find two spaces
and then replace with one space and click on Replace All.
Now, keep in mind that depending on your document,
you'll need to be thoughtful about
what you're replacing with what.
You don't want to make a change to something
that should be left as is.
So make sure to plan your Find and Replaces accordingly.
So I'm going to repeat this to finish fixing the words
that had three spaces between them.
For more tutorials like this,
click on these links. Thanks!