Delete Spaces from text download

[Guest]

Hi all, i am pulling a text file into excel and the one field, e-mails, has a
lot of spaces of variable size in it, is there a way i can delete these with
code ??

Any help would be greatly appreciated...

 

[JW]

To get rid of leading and trailing spaces:
Range("A1").Value=Trim(Range("A1").Value

To get rid of all spaces:
Range("A1").Value = Replace(Range("A1").Value, " ", "")

 

[Guest]

Select the cells and run:

Sub spacekiller()
For Each r In Selection
r.Value = Replace(r.Value, " ", "")
Next
End Sub

 

[Guest]

Thanks A million Gary"s Student, works great... :-0)

 

[Yossy]

Hi Gary I tried to use your space killer code to trim but gave error. am i
doing something wrong? This error Complile error. Variable not defined and it
highlighted the r and the sub spacekiller.

Will appreciate your help. Thanks

 

[Rick Rothstein \(MVP - VB\)]

Good... that means your are using Option Explicit in your procedure modules.
The Option Explicit statement requires all variables to be declared (really
helpful when you accidentally misspell a declared variable name later on in
your code) and 'r' is a variable that was not declared. Add this as the
first statement in the subroutine and all should be well...

Dim r As Range

Rick

 

[Yossy]

u are awesome!!!! thanks it works and also for quick response