How to Trim in Excel

In this lesson, you will learn how to use the TRIM function.

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

Trim function syntax

Syntax of TRIM function is:

=TRIM(text)

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

Trim function examples

You can also use TRIM function with other functions.

Remove extra spaces in Excel

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

=TRIM(A1)

TRIM remove extra spaces

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 the TRIM function.

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

TRIM extract string

Of course, you can modify this formula. If you have a problem with another character (e.g., a question mark), just change the full stop with this character in the formula.

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

If your string text is longer than 10, just increase it.

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

Finding words in a long string 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 with your text.

If you want to get a text without a WORD string just change the 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 the TRIM function to work better and always fine.

Find exact value in cell

The task is to find the exact value in the cell. It means that when you want to find "15", Excel will find you only cells with "15", but not with "1515", "215," "150"...

=IF(ISNUMBER(FIND(";15;",";"&TRIM(A1)&";")),15,"")

Excel find exact value

Simple Trim Formula

This formula is to trim a specific text in Microsoft Excel.

Simple Trim Formula

TRIM with Multiple Space Issues

This example focuses on using the trim function with multiple spacing issues.

TRIM with Multiple Space Issues

Trim a Cell

This example focuses on trimming text that is in a cell.

Trim a Cell

Trim and Find

This example focuses on trimming text in multiple cells. It is under these circumstances that we’d have the word layout and the texts we want to use are in different cells.

Trim and Find

TRIM and Flexibilities

We have text in different cells, but we want to add something to it. This example focuses on trimming both cells and text.

TRIM and Flexibilities

Trim and Left

This is the example that focuses on trimming and using data to the left. It is the acknowledgment of the letter in left, using trim and left functions simultaneously.

Trim and Left

Trim, Right, Substitute and Rept.

This example uses four different functions – trim, right, substitute, and rept. – to find a solid result with the simultaneous usage of the functions.

Trim Right Substitute and Rept

Trim, Left, Substitute, Mid, Find and Rept.

This example uses six different functions – trim, left, substitute, mid, find, and rept. – to get a specific result. It is being used to eliminate some texts from the row.

Trim Left Substitute Mid Find and Rept

Previous Example Excluding "Matter"

This example uses the very same data as the previous example, but this time we are excluding the word "matter" from the spreadsheet. The best part is, we are using the very same functions as the previous example – trim, left, rept., substitute, mid, and find.

Previous Example Excluding Matter

IF, ISNUMBER, FIND and TRIM

This is the example that makes use of four different functions – IF, ISNUMBER, FIND, and TRIM – to see if we would get a different result.

IF ISNUMBER FIND and TRIM