Clearing out spaces in a value

[dwarden]

Hi

I need to clear out spaces in a value. Is there formula can I use for this?

Eg.
Current value = 123 456 789
I need to change it to = 123456789

Thank you

 

[shemayisroel]

You could simply use find and replace

Highlight the data in question

Ctrl+F

Replace tab

Find what - click on it then press space bar once

Replace with - leave as is

Then press replace all

 

[carsten]

I think you are looking for a formula?

Your current value is a string.
To remove the space and convert it to a value:

=VALUE(SUBSTITUTE("123 456 789 "," ",""))

 

[shemayisroel]

For a formula solution

One way is

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 103px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana">123 456 789</TD><TD style="TEXT-ALIGN: right">123456789</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=SUBSTITUTE(A1," ","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[dwarden]

Wow that was simple...thanks for that

I did have one problem where one value turned
from this 123 3214 41234
to this 1.23321E+11

I thought it was an issue with column, undid to revert back changed the column width & tried again got the same result.

 

[dwarden]

=SUBSTITUTE(A11," ","") works....

no other issues.

Thank you all

 

[shemayisroel]

=SUBSTITUTE(A11," ","") works....

no other issues.

Thank you all

Hi David,

Thats great - your welcome.