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...
--
Les

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, " ", "")

Les wrote:
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...
--
Les

Select the cells and run:

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



--
Gary''s Student - gsnu200743


"Les" wrote:

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...
--
Les

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


"Gary''s Student" wrote:

Select the cells and run:

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



--
Gary''s Student - gsnu200743


"Les" wrote:

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...
--
Les

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

"Gary''s Student" wrote:

Select the cells and run:

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



--
Gary''s Student - gsnu200743


"Les" wrote:

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...
--
Les

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" wrote in message
...
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

"Gary''s Student" wrote:

Select the cells and run:

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



--
Gary''s Student - gsnu200743


"Les" wrote:

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...
--
Les

u are awesome!!!! thanks it works and also for quick response
"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

"Gary''s Student" wrote:

Select the cells and run:

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



--
Gary''s Student - gsnu200743


"Les" wrote:

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...
--
Les