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" <(E-Mail Removed)> wrote in message
news:490B33F7-3E94-498E-8E42-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:490B33F7-3E94-498E-8E42-(E-Mail Removed)...
> > 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
>
>