How to Trim in Excel

by Best Excel Tutorial Functions

The TRIM function in Excel is handy for cleaning up text by removing any extra spaces from a string, leaving only single spaces between words. In this lesson, you will learn how to use the TRIM function.

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 leading, trailing, and extra spaces from a text string use this simple formula =TRIM(A1)

TRIM remove extra spaces

To remove all spaces from a text string except for a single space between words use:

=TRIM(SUBSTITUTE(A1,” “,REPT(” “,1)))

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:

See also  How to Use Cubemember Function

=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

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

Use these formulas to handle various situations involving unwanted spaces or specific text extraction needs.

Related posts:

if function excel multiple conditionsIf Function with multiple conditions Default ThumbnailHow to Vlookup other Tab in Excel? vlookup another column lookup columnsHow to VLOOKUP Another Column?