# 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:

### 'TRIM' function examples

The following 11 articles contain the TRIM function.

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

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

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

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

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

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

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

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

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

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

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

### Functions in 'Text' category

The TRIM function function is one of many 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 signsUse 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 OscarYou can contact me through this contact form