Remove Leading/Trailing Blank While Keeping Blanks In Between Words In Excel

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.

Capture




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))

Capture

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.

Share The Knowledge

Random Posts

Leave a Reply