remove spaces in text in excel

[Guest]

I'm working with existing Excel data, specifically model numbers, many of
which have spaces within that model number, for example; "BXZ 123".

Could someone tell me how to remove tha space between the BXZ and the 123?
I've got columns of these numbers that I need to take the spaces out of, and
the spaces aren't always in the same place within the cell, so I can't just
specify which character to remove, and it probably goes without saying that I
can't do them one at a time.

I've tried TRIM, but that only takes those spaces off the ends, and I've
tried CLEAN, but that doesn't work, for some reason. Seems to me that by
it's definition, that should be the function that does it, but it doesn't.
I've also tried to use the "Replace" function by entering a space into the
text box that it's supposed to find, but it doesn't seem to recognize the
space as my having entered anything.

Thanks,
Matt

 

[JulieD]

Hi

try the SUBSTITUTE function
e.g.
=SUBSTITUTE(A1," ","")

Cheers
JulieD

 

[Guest]

Try

=SUBSTITUTE(Cell," ","")

if you still have "spaces" try

=SUBSTITUTE(Cell,CHAR(160),"")


Regards,

Peo Sjoblom

 

[Guest]

Julie and Peo...

Worked like a charm! Thanks a lot!

Funny, even after you both suggested that and it worked, that I can't find
that function in either of the textbooks I have for Excel... I guess I
better get some more books...LOL

Matt