string - How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula? -


in excel, trim() remove spaces before , after text, while removing duplicate spaces in between words.

is there formula or combination thereof same trim() leave spaces between words as-is?

in following example, i'm looking formula accomplish of fictitious formula "wxyz":

trim(" omicron persei 8 ") = "omicron persei 8"
wxyz(" omicron persei 8 ") = "omicron persei 8"

note i've read somewhere trim() in vba work of wxyz above. however, i'm looking formula solution.

i believe should work (assuming string located @ a1):

=mid(a1, find(left(trim(a1),1),a1), (len(a1)-match(right(trim(a1),1),index(mid(a1,len(a1)-row(indirect("1:"&len(a1)))+1,1),0),0)-find(left(trim(a1),1),a1)+2)

find(left(trim(a1),1),a1) returns location of first non-space character in string

match(right(trim(a1),1),index(mid(a1,len(a1)-row(indirect("1:"&len(a1)))+1,1),0),0) returns location of last non-space character in string from right-to-left.


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -