Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Remove Spaces in Excel 3

Status
Not open for further replies.

Wray69

Technical User
Joined
Oct 1, 2002
Messages
299
Location
US
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
 
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
 
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.
 
Sorry to double-post on your, Dreamboat. A little slow this a.a., I am.
 
Try highlighting the range, then replace spaces with nothing. Just remember to check off the "Entire Cell Only" option.
 
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," ","")
 
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.

 
Thanks Onedtent, that function works great....

Regards,
Wray
 
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~
 
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~
 
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

 
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

 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top