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 an HTML entity. This will not be removed with TRIM.
- The CLEAN function strips the first 32 non-printing characters (ASCII values 0 through 31) from text.
TRIM formula examples
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...
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 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 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 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...
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 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 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 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 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 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 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 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 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...
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
