Purpose
Remove extra spaces from text
Return value
Text with extra spaces removed.
Syntax
=TRIM(text)- text - The text from which to remove extra space.
How to use
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(CLEAN(A1)) // trim and clean
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 function examples
Extract word containing specific text
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 inside out, the original text in B5 is flooded with spaces using SUBSTITUTE:...
Sort comma separated values
In this example the goal is to sort the comma separated values in column B in alphabetical order. In the latest version of Excel, you can solve this problem with a formula based on TEXTSPLIT, SORT, and TEXTJOIN. In earlier versions of Excel the problem is more complicated. See below for a couple of...
Split text with delimiter
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 rid of the extra space. In this snippet, the delimiter ( delim ) is replaced with...
Remove leading and trailing spaces from text
The TRIM function is fully automatic. It removes both leading and trailing spaces from text strings, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a reference to a cell. TRIM with CLEAN If you also need to remove line breaks from cells...
Get last line in cell
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. Working from the inside out, we use the SUBSTITUTE function to find all line...
Join cells with comma
Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (...
Extract nth word from text string
In this example, the goal is to extract the nth word from the text string given in column B. The article below explains two approaches. The first approach is based on the new TEXTSPLIT function , which makes it very easy to split text with a custom delimiter. The second approach is a more...
Convert text date dd/mm/yy to mm/dd/yy
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 follows: 1. The year value is extracted with the RIGHT function: RIGHT(B5,2)+2000...
Get last word
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 find all spaces in the text, and replace each space with 100 spaces: SUBSTITUTE(B6...
Normalize text
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 each SUBSTITUTE replacing one character with a single space, then handing off to...
Count total words in a cell
In this example, the goal is to count the total number of words in a cell. Excel doesn't have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best...
Count total words in a range
For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of...
Cell equals one of many things
In this example, the goal is to return a TRUE or FALSE result for each value in column B, based on whether it appears in the range E5:E9, which has been named "things" for convenience. Context Imagine you have a list of values in the range B5:B16 and you want to check each value to see if it...
LAMBDA count words
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 this example, the base formula is: =LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(...
Extract word that begins with specific character
Starting from the inside out, the MID function is used to extract all text after "@": MID(B5,FIND("@",B5),LEN(B5)) The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text. This is a bit sloppy, but it avoids having to calculate the...
TRIM function videos
How to clean text with CLEAN and TRIM
When you bring data into Excel you sometimes end up with extra spaces and other characters that cause problems. Excel contains two functions that can help you clean things up. Let's take a look. Here we have a list of movie titles that were copied in from some other system. You can see that there's...
How to build all-in-one formulas
In this video we're going to build a formula that counts the number of words in a cell. This cell has eight words in it, and we're going to build a formula step by step that gives us the number "8". We're going to use "helper" formulas to do that, and when we're finished, we'll take those helper...
Related functions
CLEAN Function
The Excel CLEAN function takes a text string and returns text that has been "cleaned" of line breaks and other non-printable characters.
