MrExcel.com - Your One Stop for Excel Tips & Solutions

Remove all spaces in string

Posted by Andy Gee on January 22, 2002 8:57 AM

Please, does anyone know how to remove all the spaces from a string with variable amounts of spaces.

Many thanks in advance

Posted by Aladin Akyurek on January 22, 2002 9:06 AM

E.g.: =SUBSTITUTE(A1," ","") [NT]

Posted by Andy Gee on January 22, 2002 9:15 AM

Thanks!

Thanks! I knew it'd be something simple! Thanks again. Andy

Posted by Steve Hartman on January 22, 2002 9:38 AM

=SUBSTITUTE(A1," ","") is incomplete

This will only remove instances of a single space. To remove all spaces, including variable length spaces, first use the trim function to remove all but the single spaces and then use substitute:

=SUBSTITUTE(TRIM(A1)," ","")

Posted by Steve Hartman on January 22, 2002 9:49 AM

Re: =SUBSTITUTE(A1," ","") is incomplete

I plead temporary senility. There is of course no need to trim first. My apologies to all especially Aladin.

Posted by IML on January 22, 2002 9:50 AM

Can you give an example of different results with yours? Thanks. (NT)

Posted by Russell Hauf on January 22, 2002 9:51 AM

TRIM not needed

Substitute will substitute ANY and ALL instances of whatever argument you give it - therefore, if you specify one space, it will remove all of them. Give it a try!

-rh