error clean spaces formula

[Manolocs]

Hello, for some reason this formula clean one space only in the cell, I have to use the formula several times to get ride of all spaces in the cel . Can someone help me? Thanks in advance....

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

 

[Caribeiro77]

I don't know your kind of data but with this bellow it works...

Book1
	A
1	asdd asd asdasdasd asfasfas asfasfasas
2
3	asddasdasdasdasdasfasfasasfasfasas
Sheet1

Cell Formulas
Range	Formula
A3	=TRIM(SUBSTITUTE(A1," ",""))

 

[Manolocs]

Sorry Caribeiro77, I did not express myself well, I have text in the cells, I need a space between each word for obvious reason, but my formula is leaving one space at the beginning of the text, I can not get rid of this space even if I use the formula again. But Thank you for the formula you show me will be useful in the future.

 

[Caribeiro77]

Do you have 2 or 3 examples you can provide us?

 

[Manolocs]

Do you mind in take a look on this example?

https://dl.dropboxusercontent.com/u/14313867/Test to Caribeiro.xlsx

 

[Caribeiro77]

I don't know why, you have 2 extra spaces in the beggining of each cell.....Try:

	A	B
Air	Air
Alerting	Alerting
Alternate	Alternate
Ground	Ground
Ceiling.	Ceiling.
Certificate	Certificate
Chairman:	Chairman:
Change	Change
Clearance Limit.	Clearance Limit.
Common Carriage.	Common Carriage.
Control Area.	Control Area.
Traffic	Traffic
Large	Large

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=TRIM(MID(A1,3,LEN(A1)-2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Manolocs]

This formula is deleting the two first spaces of the text, if the text has only one space in front then the formula deletes the first letter of the text
By the way great tutorial on youtube very useful

 

[Caribeiro77]

One more try....

Book1
	A	B
1	Air	Air
2	Alerting	Alerting
3	Alternate	Alternate
4	Ground	Ground
5	Ceiling.	Ceiling.
6	Certificate	Certificate
7	Chairman:	Chairman:
8	Change	Change
9	Clearance Limit.	Clearance Limit.
10	Common Carriage.	Common Carriage.
11	Control Area.	Control Area.
12	Traffic	Traffic
13	Large	Large
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(MID(A1,FIND(CHAR(160),A1)+1,LEN(A1)-1)),A1)

 

[Manolocs]

;( did not work, same as before the spaces are still there. Did you manage to make work in the spreadsheet I sent?

 

[shg]

In the formula you have in your workbook, what looks like a space in the SUBSTITUTE function is actually a non-breaking space, so it does nothing:

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"*")))

That asterisk is how an NBS appears in the forum.

Fix that and it works fine.