The post demonstrates how we could remove leading and trailing spaces in excel while keeping the blanks in between words in excel.
TRIM or CLEAR function in excel could remove the leading and trailing spaces but they also compressed the blanks in between the words.
In the pic below you could see that our data in column A contains leading, trailing and multiple blank spaces in between the words and we want the output as shown in cell B2 with no leading and trailing space but the blank spaces in between intact.
Step 1
Insert the below formula in cell B2.
=MID(A2,FIND(LEFT(TRIM(A2),1),A2),(LEN(A2)-MATCH(RIGHT(TRIM(A2),1),INDEX(MID(A2,LEN(A2)-ROW(INDIRECT(“1:”&LEN(A2)))+1,1),0),0)-FIND(LEFT(TRIM(A2),1),A2)+2))
This part in formula FIND(LEFT(TRIM(A1),1),A1) gives the location of the first non space character in the string.
Hope this helped.
