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 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 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(...
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($...
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...
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 copy...
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 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...
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...
To extract the page, or the part of a path after the last forward slash (/), you can use a formula based on several Excel functions: TRIM, RIGHT, SUBSTITUTE, and REPT. In the example shown, the formula in C5 is:
=TRIM...
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...
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 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 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("...
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 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...
Related videos
This video shows you how to use two text functions, CLEAN and TRIM, to strip extra spaces and remove line breaks from text.
In this video, we build a formula that counts words in a cell with the LEN and SUBSTITUTE functions.
Related functions
Good links
