How to use the TRIM function

Author: Oscar Cronquist Article last updated on May 17, 2022

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:

=TRIM(B3)

TRIM function Syntax
TRIM function Arguments
TRIM function not working
TRIM function all cells
TRIM function for the entire column
Trim characters from left (Link)
Trim characters from right (Link)
Get Excel *.xlsx file

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:

=TRIM(SUBSTITUTE(B3, CHAR(160), " "))

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

TRIM function remove leading space characters

Formula in cell C3:

=MID(B3, SEARCH(LEFT(TRIM(B3), 1), B3), LEN(B3))

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

TRIM function remove only trailing space characters1

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:

=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))))))

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

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:

=LET(x, B3, y, SEQUENCE(, LEN(x)), MID(x, 1, MAX(IFERROR(SEARCH(" ", MID(x, y, 1)), y))))

'TRIM' function examples

The following 11 articles contain the TRIM function.

Count identical values if they are on the same row
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]

Extract last word in cell
Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]

Extract n-th word in cell value
The formula displayed above in cell range D3:D9 extracts a word based on its position in a cell value. For […]

Extract values between two given delimiting strings
The image above demonstrates a rather small formula in cell D3 that extracts values in cell B3 based on two […]

How to count blank cells
The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

How to create name initials
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]

How to use the COUNTIF function to count not blank cells
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

If cell contains text from list
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

Text to columns: Split words in a cell
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

Functions in this article

TRIM SUBSTITUTE CHAR MID SEARCH LEFT LEN MAX IFERROR ROW LET SEQUENCE

Functions in 'Text' category

The TRIM function function is one of many functions in the 'Text' category.

ARRAYTOTEXT function

Returns an array of text values from a given cell range.

ASC function

Converts full-width (double-byte) characters to half-width (single-byte) characters.

CHAR function

Converts a number to the corresponding ANSI character determined by your computers character set.

CLEAN function

Deletes the first 32 nonprinting characters in 7-bit ASCII code in your argument.

CODE function

Returns the corresponding number for the first character based on your computers character set. (PC- ANSI)

CONCAT function

Concatenates values from multiple cells.

CONCATENATE function

Joins text strings.

EXACT function

Checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.

FIND function

Returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

LEFT function

Extracts a specific number of characters always starting from the left.

LEN function

Returns the number of characters in a cell value.

LOWER function

Converts a value to lower case letters.

MID function

Returns a substring from a string based on the starting position and the number of characters you want to extract.

REPLACE function

Substitutes a part of a text string based on the number of characters and length with a text string you provide.

REPT function

Repeats a specific text a chosen number of times.

RIGHT function

Extracts a specific number of characters always starting from the right.

SEARCH function

Returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SUBSTITUTE function

Replaces a specific text string in a value. Case sensitive.

T function

Returns a text value if the argument is a text value.

TEXT function

Converts a value to text in a specific number format.

TEXTAFTER function

Extracts a string after a specific substring in a given value.

TEXTBEFORE function

Extracts a string before a specific substring from a given value.

TEXTJOIN function

Combines text strings from multiple cell ranges.

TEXTSPLIT function

Splits a string into an array based on delimiting values.

TRIM function

Deletes all blanks or space characters except single blanks between words in a cell value.

UNICHAR function

Calculates a character based on a Unicode number.

UNICODE function

Returns a Unicode number based on a character.

UPPER function

Converts a value to upper case letters.

VALUE function

Converts a text string that represents a number to a number.

VALUETOTEXT function

Returns a value in text form. Text values are unmodified and non-text values are converted to text.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the TRIM function

Table of Contents

1. TRIM Function Syntax

2. TRIM Function Argument

3. TRIM function not working

3.1 Explaining formula

Step 1 - Create HTML space character

Step 2 - Replace HTML character with a regular space character

Step 3 - Remove leading and trailing space characters

4. Remove leading space characters only

Explaining formula in cell C4

Step 1 - Remove leading, trailing, and repeated space characters

Step 2 - Extract the first character

Step 3 - Search for the first character

Step 4 - Count characters

Step 5 - Extract string based on character position and string length

5. Remove trailing space characters only

Explaining formula in cell C4

Step 1 - Count characters

Step 2 - Create dynamic cell reference

Step 3 - Create a cell reference to a cell range

Step 4 - Create numbers from 1 to n

Step 5 -Extract string based on character position and string length

Step 6 - Search for blanks

Step 7 - Replae errors with corresponding number

Step 8 - Calculate the largest number in the array

Step 9 - Extract string from value

Excel 365 formula

Excel 'Text manipulation' functions

'TRIM' function examples

Functions in this article

Functions in 'Text' category

Excel function categories

Excel categories

Leave a Reply

How to comment