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

  • Hide Error In Pivot Table In Excel

    Pivot table comes to rescue when we want some quick aggregation in a short notice.We could quickly summarize our data and […]

    Share The Knowledge
  • Find Asterisk In Excel

    In this post we  will learn about how to find asterisk in excel.We may find some special character and wildcard […]

    Share The Knowledge
  • Sum Numbers Separated By Symbol In Excel
    Sum Numbers Separated By Symbol In Excel

    sum numerical values separated by symbol in excel, excel formula to sum numerical values separated by symbol in excel, excel formula to remove symbol and calculate sum, sum numerical values separated by @ in excel,
    sum numerical values separated by comma in excel

    Share The Knowledge
  • Extract First Sentence In Excel

    Let’s see how we could extract the first sentence in a paragraph n excel. There are scenarios when are faced […]

    Share The Knowledge

Leave a Reply