Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

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

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


Thanks!

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

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


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

Posted by Steve Hartman on January 22, 2002 9:38 AM
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)," ","")


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

Posted by Steve Hartman on January 22, 2002 9:49 AM
I plead temporary senility. There is of course no need to trim first. My apologies to all especially Aladin.


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

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


TRIM not needed

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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.