Eliminate all spaces in a text string

[Luis Ah Hoy Jr]

A fellow worker sent me a spreadsheet full of cells formatted in such a way that it makes it difficult to remove some of the spaces.

For testing purposes, here's the file:
Uploadfiles.io - remove all spaces.xlsx

In this example, I have this text string in cell A1: " 6403 "
I want to remove all spaces so that it only remains as: "6403".

In this case, the TRIM() and the SUBSTITUTE() methods only work to eliminate the spaces after the number, but they both fail to eliminate the spaces before it.

Any idea?

 

[Fluff]

Have you tried the clean function?
=Clean()

 

[Luis Ah Hoy Jr]

Thanks for trying, Fluff.
Unfortunately in this case, the CLEAN function doesn't eliminate any space at all.

 

[FormR]

Hi, your spaces are not normal spaces - you can try like this to remove them.

=0+SUBSTITUTE(A1,CHAR(160),"")

Or you can do Find > Replace in the "Find what" field hold the ALT Key and type 0160 on the keyboard number pad - leave replace with blank.

 

[Luis Ah Hoy Jr]

That worked, FormR. Many thanks.

There's this other method that did the trick for me, though it's a bit more complex than yours:
=TRIM(SUBSTITUTE(A1;CHAR(160);CHAR(32)))

They probably provide different results when applied in some other case(s). But in this particular problem they worked equally well.

 

[Morel]

You may need to delete all blanks, like in a formula chain. To do this, you can create a helper column and enter the formula: =SUBSTITUTE(A1," ","")
Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.