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 leading, trailing, and extra spaces from a text string use this simple formula.
=TRIM(A1)
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))
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))
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))
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,””)
Reversing Text
If you need to reverse the order of characters within a text string, you can use a combination of functions, including TRIM. Let’s say you have text in cell A1, and you want to reverse it:
=TRIM(MID(A1,LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1)))+1,1))
This formula reverses the characters in the text while removing any extra spaces that might occur during the process.
Removing Numbers from Text
To remove numbers from a text string, you can use a combination of TRIM and the SUBSTITUTE function. Let’s say you have text in cell A1:
=TRIM(SUBSTITUTE(A1,”1″,””)&SUBSTITUTE(A1,”2″,””)&SUBSTITUTE(A1,”3″,””)&SUBSTITUTE(A1,”4″,””)&SUBSTITUTE(A1,”5″,””)&SUBSTITUTE(A1,”6″,””)&SUBSTITUTE(A1,”7″,””)&SUBSTITUTE(A1,”8″,””)&SUBSTITUTE(A1,”9″,””)&SUBSTITUTE(A1,”0″,””))
This formula substitutes each digit from 0 to 9 with an empty string, effectively removing all numbers from the text.
Leave a Reply