Delete Spaces from text download
 

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

Delete Spaces from text download
 

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

Delete Spaces from text download
 

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

Delete Spaces from text download
 

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

Delete Spaces from text download
 

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

Delete Spaces from text download
 

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

Delete Spaces from text download
 

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