How to use the TRIM function
The picture above shows you values that have multiple leading and trailing blanks, sometimes also multiple blanks between words.
The TRIM function deletes all blanks or space characters except single blanks between words in a cell value.
Formula in cell C3:
Table of Contents
1. TRIM Function Syntax
TRIM(text)
2. TRIM Function Argument
| text | The string or cell value you want to delete blanks from. |
3. TRIM function not working
The TRIM function removes the space character from a cell value, however, there is another space character commonly used in HTML pages that are not removed by this function.
To remove that space character you need to use the SUBSTITUTE function with the TRIM function. Formula in cell C3:
CHAR(160) returns the other space character that TRIM can't delete. The SUBSTITUTE function then replaces the HTML space character with a regular space character.
3.1 Explaining formula
Step 1 - Create HTML space character
The CHAR function converts a number to the corresponding character based on your computer's character set.
CHAR(160)
returns " ". This is an HTML space character.
Step 2 - Replace HTML character with a regular space character
The SUBSTITUTE function replaces a specific text string in a value.
SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(B3, CHAR(160), " ")
becomes
SUBSTITUTE("Martin Williams"," "," ")
and returns
"Martin Williams".
Step 3 - Remove leading and trailing space characters
TRIM(SUBSTITUTE(B3, CHAR(160), " "))
becomes
TRIM("Martin Williams")
and returns "Martin Williams".
4. Remove leading space characters only
Formula in cell C3:
Explaining formula in cell C4
Step 1 - Remove leading, trailing, and repeated space characters
TRIM(B4)
becomes
TRIM(" Martin Williams ")
and returns
"Martin Williams"
Step 2 - Extract the first character
The LEFT function extracts a specific number of characters starting from the left.
LEFT(text, [num_chars])
LEFT(TRIM(B3),1)
becomes
LEFT("Martin Williams",1)
and returns "M".
Step 3 - Search for the first character
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])
SEARCH(LEFT(TRIM(B3),1),B3)
becomes
SEARCH("M", " Martin Williams ")
and returns 2.
Step 4 - Count characters
The LEN function returns the number of characters in a cell or string.
LEN(B3)
becomes
LEN(" Martin Williams ")
returns 21.
Step 5 - Extract string based on character position and string length
The MID function extracts a substring from a string based on the starting position and the number of characters you want to extract.
MID(text, start_num, num_chars)
MID(B3, SEARCH(LEFT(TRIM(B3), 1), B3), LEN(B3))
becomes
MID(B3, 2, 21)
becomes
MID(" Martin Williams ", 2, 21)
and returns "Martin Williams ".
5. Remove trailing space characters only
The values in columns B and C are right-aligned in order to show that there are multiple trailing space characters in cells B4 and B6.
Excel formula in cell C3:
Explaining formula in cell C4
Step 1 - Count characters
The LEN function returns the number of characters in a cell or string.
LEN(B3)
becomes
LEN(" Martin Williams ")
and returns 29.
Step 2 - Create dynamic cell reference
The INDEX function is most often used to get values, however, it can also be used to create cell references.
INDEX($A$1:$A$1000, LEN(B3))
becomes
INDEX($A$1:$A$1000, 20)
and returns $A$20.
Step 3 - Create a cell reference to a cell range
The colon character lets you build cell references in an Excel formula.
$A$1:INDEX($A$1:$A$1000, LEN(B3))
returns $A$1:$A$20.
Step 4 - Create numbers from 1 to n
The ROW function returns a number representing the row number of a given cell reference.
ROW($A$1:INDEX($A$1:$A$1000, LEN(B3)))
becomes
ROW($A$1:$A$20)
and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}.
Step 5 -Extract string based on character position and string length
The MID function extracts a substring from a string based on the starting position and the number of characters you want to extract.
MID(text, start_num, num_chars)
MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)
becomes
MID(" Martin Williams ", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}, 1)
and returns
{" "; " "; " "; " "; " "; "M"; "a"; "r"; "t"; "i"; "n"; " "; "W"; "i"; "l"; "l"; "i"; "a"; "m"; "s"; " "; " "; " "; " "; " "; " "; " "; " "; " "}.
Step 6 - Search for blanks
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])
SEARCH(" ", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1))
becomes
SEARCH(" ", {" "; " "; " "; " "; " "; "M"; "a"; "r"; "t"; "i"; "n"; " "; "W"; "i"; "l"; "l"; "i"; "a"; "m"; "s"; " "; " "; " "; " "; " "; " "; " "; " "; " "})
and returns {1; 1; 1; 1; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; 1; 1; 1; 1; 1; 1; 1; 1}.
Step 7 - Replae errors with corresponding number
The IFERROR function handles error values.
IFERROR(value, value_if_error)
IFERROR(SEARCH(" ", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)), ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))))
becomes
IFERROR({1; 1; 1; 1; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; 1; 1; 1; 1; 1; 1; 1; 1}, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))))
becomes
IFERROR({1; 1; 1; 1; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; 1; 1; 1; 1; 1; 1; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20})
and returns
{1; 1; 1; 1; 1; 6; 7; 8; 9; 10; 11; 1; 13; 14; 15; 16; 17; 18; 19; 20; 1; 1; 1; 1; 1; 1; 1; 1; 1}.
Step 8 - Calculate the largest number in the array
The MAX function returns the largest number in an array or cell range.
MAX(number1, [number2], ...)
MAX(IFERROR(SEARCH(" ", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)), ROW($A$1:INDEX($A$1:$A$1000, LEN(B3)))))
becomes
MAX({1; 1; 1; 1; 1; 6; 7; 8; 9; 10; 11; 1; 13; 14; 15; 16; 17; 18; 19; 20; 1; 1; 1; 1; 1; 1; 1; 1; 1})
and returns 20.
Step 9 - Extract string from value
The MID function extracts a substring from a string based on the starting position and the number of characters you want to extract.
MID(text, start_num, num_chars)
MID(B3, 1, MAX(IFERROR(SEARCH(" ", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)), ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))))))
becomes
MID(" Martin Williams ", 1, 20)
and returns " Martin Williams".
Excel 365 formula
Excel 365 formula in cell C3:
Useful links
TRIM function - Microsoft support
Excel TRIM function - quick way to remove extra spaces
'TRIM' function examples
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Table of Contents Count cells containing text from list Count entries based on date and time Count cells with text […]
Table of Contents Extract first word in cell value Extract the first word in cell - return warning if not […]
Functions in 'Text' category
The TRIM function function is one of 29 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form