Excel TRIM Function
Summary
The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.
Purpose
Remove extra spaces from text
Return value
Text with extra spaces removed.
Syntax
=TRIM (text)
Arguments
- text - The text from which to remove extra space.
Usage notes
- TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text.
- TRIM is useful when cleaning up text that has come from other applications or environments.
- TRIM only removes the ASCII space character (32) from text.
- Unicode text often contains a non-breaking space character (160) that appears in web pages as the HTML entity, . This will not be removed with TRIM. See the Microsoft article Remove spaces and non-printing characters from text for more information.
TRIM formula examples
To remove some of the natural complexity of text (strip punctuation, normalize case, remove extra spaces) you can use a formula based on the SUBSTITUTE function, with help from the TRIM and LOWER functions.
Context...
If you want to count the total words in a range of cells, you can do with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula above, rng represents a range of...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. In the example shown, the formula in C6 is:
=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(...
If you need to clean up names that are not in a proper case, you can use a simple formula based on PROPER functions.
In the example the formula in C5 is:
=PROPER(B5)
How this formula works
The PROPER function...
To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user...
To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. In the example...
To split text at an arbitrary delimiter (comma, space, pipe, etc.) you can use a formula based on the TRIM, MID, SUBSTITUTE, REPT, and LEN functions. In the example shown, the formula in C5 is:
=TRIM(MID(SUBSTITUTE($...
If you need to get the middle name from a full name, and you already have the first and last names in separate cells, you can use a formula that extracts the middle name using the MID and LEN functions, with help from...
If you need to strip leading and trailing spaces from text in one or more cells, you can use the TRIM function.
In the example show, the formula in cell C3 is:
=TRIM(B3)
Once you've removed extra spaces, you can...
To count the total words in a cell, you can use a formula based on the LEN and SUBSTITUTE functions. In the example shown, C3 contains this formula:
=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))+1
How...
To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
In the example shown, the formula in D5 is:
=TRIM(MID(...
To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or...
To convert dates in text format dd/mm/yy to a true date in mm/dd/yy format, you can use uses a formula based on the DATE function. In the example shown, the formula in C5 is:
=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions.
In the example shown, the formula in C5 is:
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT("...
If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
How...
Related videos
When you bring data into Excel you sometimes end up with extra spaces and line breaks. This short video shows you how to clean things up with TRIM and CLEAN.
Related functions
Good links
