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.
Version
Usage notes
The TRIM function strips extra spaces from text, leaving only a single space between words, and removing any leading or trailing space. For example:
=TRIM(" A stitch in time. ") // returns "A stitch in time."
The TRIM function can be used together with the CLEAN function to remove extra space and strip out other non-printing characters:
TRIM often appears in other more advanced text formulas. For example, the formula below will count the number of words in cell A1:
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
Because this formula depends on single spaces to get an accurate word count, TRIM is used to normalize space before the count is calculated. Full description here.
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
At the core, this formula looks for a line delimiter ("delim") and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.
Note: In older versions of Excel on a Mac, use CHAR(13) instead of...
The PROPER function automatically reformats text so that all words are capitalized. At the same time, it lowercases all other text.
If you also need to strip out extra spaces in the names, you can wrap PROPER in the...
This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right....
The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from the...
Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (&) and a single space between each value:
B5&" "&C5&" "&D5...
The formula shown in this example uses a series of nested SUBSTITUTE functions to strip out parentheses, hyphens, colons, semi-colons, exclamation marks, commas, and periods. The process runs from the inside out, with...
In this example, the goal is to extract the nth word from the text string given in column B. Excel does not currently have a function dedicated to this task, so this formula is a workaround. At the core, this formula...
The TRIM function is fully automatic. It removes removes both leading and trailing spaces from text, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a...
Excel doesn't have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example...
The LAMBDA function can be used to create reusable, custom functions in Excel without VBA or macros. The first step in creating a LAMBDA function is to verify the formula logic needed in a standard Excel formula. In...
Assuming you have a full name in column B, a first name in column C, and a last name in column D, you can use a formula that looks like this:
=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))
At the core, the MID...
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as...
The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get...
At the core, this formula replaces all forward slashes (/) with 100 spaces, then extracts 100 characters from the RIGHT and cleans this up with the TRIM function.
The replacement is done with SUBSTITUTE and REPT here...
This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.
Working from the inside out, we use the SUBSTITUTE function to...
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
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
