Remove Spaces in Excel 3

[Wray69]

I have some data that has been imported into excel from a database. It is a column of numbers that has random rondom spaces in the numbers. So the data apears as such;

45 65
4 45
456 95
4567
45 159

I need to take the spaces out and make them one number;

4565
445
45695
4567
45159

anyone have any ideas?

Regards,
Wray

 

[Dreamboat]

Sure. If you're positive they're spaces, select the entire row/column. Hit Ctrl+H. In the Find What box, type a space. In the Replace With box--don't put anything. Hit Replace All. Anne Troy

http://www.TheOfficeExperts.com

 

[carrr]

Try highlighting the column, the use Edit, Replace, leave both fields blank and hit replace all.
If this doesn't work, try hitting the space bar once in the first field, then leave the second field blank, and hit replace.

 

[carrr]

Sorry to double-post on your, Dreamboat. A little slow this a.a., I am.

 

[entaroadun]

Try highlighting the range, then replace spaces with nothing. Just remember to check off the "Entire Cell Only" option.

 

[onedtent]

your best bet:

highlight cells involved

go to Edit, Replace

in the Find box type a space and then click Replace All

or you could use
=substitute(cell_ref," ","&quot

 

[Wray69]

Thanks guys, that is actually what I do now. I was looking for a function that might accomplish this for me instead of having to go in and manually do it.

 

[Wray69]

Thanks Onedtent, that function works great....

Regards,
Wray

 

[xlbo]

as a UDF:
Function RemSpace(rng as range)
mStr = rng.text
nStr = ""
for i = 1 to len(mStr)
if mid(mStr,i,1) <> chr(32) then
nStr = nStr & mid(mStr,i,1)
else
end if
next i
RemSpace = val(nStr)
end function

in code:
Place the following in a public module and run &quot;ReplaceAllSpaces&quot;

Function RemSpace(rng As String)
mStr = Range(rng).Text
nStr = &quot;&quot;
For i = 1 To Len(mStr)
If Mid(mStr, i, 1) <> Chr(32) Then
nStr = nStr & Mid(mStr, i, 1)
Else
End If
Next i
RemSpace = Val(nStr)
End Function


Sub replaceAllSpaces()
For Each c In Range(&quot;A1:A&quot; & Range(&quot;A65536&quot.End(xlUp).Row)
c.Value = RemSpace(c.Address)
Next c
End Sub

Rgds
~Geoff~

 

[xlbo]

d'oh - knew there was a function for it somewhere - note tho - if you want the result to be a number rather than text, use
=value(substitute(cell_ref,&quot; &quot;,&quot;&quot)
Rgds
~Geoff~

 

[schat]

suggesting =value(substitute(cell_ref,&quot; &quot;,&quot;&quot) is a nice pick up, xlbo. just to exhaust the possibilities and to save an extra function call you could also use =substitute(cell_ref,&quot; &quot;,&quot;&quot+0 to coerce a number format rather than text.

schat

 

[schat]

suggesting =value(substitute(cell_ref,&quot; &quot;,&quot;&quot) to retain the number format is a nice pick up, xlbo. just to exhaust the possibilities and to save an extra function call you could also use =substitute(cell_ref,&quot; &quot;,&quot;&quot+0 to coerce a number format rather than text.

schat

 

[TTPicasso]

Alternatively, rather than fixing a problem at the target end, cure it at the database end and pass into Excel the data in the correct format.