We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
There are a lot of steps to cleaning up a spreadsheet, especially if you’re working with raw data. A common need is to remove spaces at the end and the beginning of a cell in Excel.
This is super easy to do using Excel’s TRIM function.
How to Use the TRIM Function in Excel
WATCH: How to Use the TRIM Function in Excel (video tutorial)
What does the TRIM Function do in Excel?
TRIM, one of Excel’s Text functions, removes extra spaces from the beginning and end of a cell in Excel.
These extra spaces can be the result of formatting changes, mistakes in data entering, or other errors.
Why Use the TRIM Function
Having extra spaces in cells can affect how you can use the data in your spreadsheet.
Sorting your data, for example, won’t work correctly if your cells have extra spaces in the front.
Extra spaces at the end of cells can make it hard to correctly join text from different cells or use other features like Flash Fill.
Using TRIM to clean up these spaces will make your spreadsheet easier to use.
Ways to Use the TRIM Function in Excel
You can use TRIM to get rid of extra spaces one cell at a time or you can select a range of cells.
3 Steps to Use TRIM in an Excel Cell:
- Find a space in your spreadsheet where you can enter the TRIM function – next to your data is usually the easiest
- Type =TRIM(cell to TRIM)
- In the parentheses, enter the cell address that you want to trim extra spaces from
- Press Enter
Apply the TRIM Function to Multiple Cells at the Same Time
You can also apply TRIM to a range of cells at once either by:
- Dragging down your original TRIM function
- Using copy and paste to apply it to other cells
- Referencing the cell range in your TRIM function or
- Assigning a Defined Name to your data range and referencing that name in your TRIM Function
NOTE: When you reference a range of cells inside your TRIM function, the results will be grouped (you’ll see a blue outline if you place your cursor in one of the results). This means that they cannot be edited or deleted. Doing so will cause an error for all the results in the group. Unless…
You replace the TRIM function results with only their values (i.e., removing the calculation).
To do this:
- Copy the TRIM results
- Right-click and select Paste Special
- Select Paste Values
You can also choose to keep your original data and the results of your TRIM function separately.
You decide!
What does the TRIM Function NOT Do?
The TRIM function only removes spaces from the beginning and end of a cell.
By, itself, it does not remove spaces between words or characters.
NEXT: How to remove extra spaces between words + line breaks from cells
These statements are NOT TRUE:
‘The TRIM function only removes spaces from the beginning and end of a cell.’
‘By, itself, it does not remove spaces between words or characters.’
If you have a double space between characters it will reduce it to a single space.