TRIM function

In this lesson you can learn how to use TRIM function.

Thanks to TRIM function you can remove spaces from the text.

Syntax of TRIM function is:


If you have text with extra spaces in A1 cell, just writhe a formula =TRIM(A1). TRIM function remove extra spaces which you doesn't need.

You can also use TRIM function with other functions.

Example 1 Remove extra spaces in Excel

To remove spaces you don't need just use TRIM function. Use this simple formula.


TRIM remove extra spaces

Example 2 Extract text after the last full stop in a text string

You want to extract text after the last string. Just use the formula below with TRIM function.

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",10)),10))

TRIM extract string

Of course you can modify this formula. If you have problem with other character (eg. question mark) just change full stop with this character in formula.

=TRIM(RIGHT(SUBSTITUTE(A1,"?",REPT(" ",10)),10))

If your string text is longer than 10 just incease it.

=TRIM(RIGHT(SUBSTITUTE(A1,"?",REPT(" ",1000)),1000))

Example 3 Finding word in a long sting of text

You want to find some word in a long string of text and you don't know how to do it? Try this formula:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("WORD",A1&"WORD"),1000),"*",REPT(" ",1000)),1000))

TRIM find text word string

Just replace WORD to your text.

If you want to get a text without WORD string just change formula to:

=TRIM(LEFT(SUBSTITUTE(MID(A1;FIND("WORD";A1&"WORD")+5;1000);"*";REPT(" ";1000));1000))

TRIM find text without word string


These 2 above formulas should work also without TRIM function. Use them with TRIM function to work better and always fine.

Example 4 Find exact value in cell

Task is to find exact value in the cell. It means that when you wany to find '15', Excel will find you only cells with '15', but not with '1515', '215', '150'...


Excel find exact value