Removing spaces from text

[SimonHughes]

Hello,

I am using Excel 2010.

I need to remove spaces from a series of numbers but the spaces are in different places. So there are two spaces to remove in row 1, two in row 2, 1 in row three and so on. Is there a single formula that will do this or do I need to run a series of formula? YOur help appreciated.

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=218><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6509" width=178><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; WIDTH: 30pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=40> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; WIDTH: 134pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=178>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">0511 2568 36</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">12 59877 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">365569 123</TD></TR></TBODY></TABLE>

 

[Jonmo1]

Try Substitute...

=SUBSTITUTE(A1," ","")

Hope that helps.

 

[SimonHughes]

Aaaahhhhhhh! I feel such a fool. Of course yes, many thanks.

 

[Jonmo1]

Glad to help, thanks for the feedback..

Also, the substitute function returns a TEXT string, so your numbers will not really be numbers.

Perhaps adding +0 to the end of the formula, that will convert it to a real number.

 

[SimonHughes]

Good tip, many thanks.