I have a web query that pulls a string of data in which I only need
the first 8 characters.

Any way I can have a macro that will delete any characters to the
right of the 8th character in certain string/columns (multple columns
- A, G, & L)?

I will run this after the data is retrieved.

Thanx

How about this? James

Sub Left8()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If VarType(cell) = vbString Then
If cell.Column = 1 Or cell.Column = 7 Or cell.Column = 12 Then
If Len(cell) > 8 Then cell = Left(cell, 8)
End If
End If
Next cell
End Sub

"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a web query that pulls a string of data in which I only need
> the first 8 characters.
>
> Any way I can have a macro that will delete any characters to the
> right of the 8th character in certain string/columns (multple columns
> - A, G, & L)?
>
> I will run this after the data is retrieved.
>
> Thanx
>

Sub aldridge()
Dim r(3) As Range
Dim l(3) As Long

Set r(0) = Range("A1")
Set r(1) = Range("G1")
Set r(2) = Range("L1")

l(0) = Cells(Rows.Count, "A").End(xlUp).Row
l(1) = Cells(Rows.Count, "G").End(xlUp).Row
l(2) = Cells(Rows.Count, "L").End(xlUp).Row

For i = 0 To 2
For j = 0 To l(i)
v = r(i).Offset(j, 0).Value
v = Left(v, 8)
r(i).Offset(j, 0).Value = v
Next
Next
End Sub

--
Gary''s Student - gsnu2007


"J.W. Aldridge" wrote:

> I have a web query that pulls a string of data in which I only need
> the first 8 characters.
>
> Any way I can have a macro that will delete any characters to the
> right of the 8th character in certain string/columns (multple columns
> - A, G, & L)?
>
> I will run this after the data is retrieved.
>
> Thanx
>
>

here's another approach:

Sub trimcells()
Dim i As Variant
Dim j As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim lastrow As Long

For Each i In Array("A", "G", "L")
lastrow = ws.Cells(Rows.Count, i).End(xlUp).Row
For j = 1 To lastrow
ws.Cells(j, i).Value = Left(ws.Cells(j, i).Value, 8)
Next
Next

End Sub

--


Gary


"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a web query that pulls a string of data in which I only need
> the first 8 characters.
>
> Any way I can have a macro that will delete any characters to the
> right of the 8th character in certain string/columns (multple columns
> - A, G, & L)?
>
> I will run this after the data is retrieved.
>
> Thanx
>

Your code is an improvement over mine. If we are REALLY careful about
defining the range, we can eliminate all the Ifs and Fors:

Sub aldridge()
Set r1 = Range("A:A,G:G,L:L")
Set r2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
Set r = Intersect(r1, r2)
r.Value = Left(r.Value, 8)
End Sub


--
Gary''s Student - gsnu2007


"Zone" wrote:

> How about this? James
>
> Sub Left8()
> Dim cell As Range
> For Each cell In ActiveSheet.UsedRange
> If VarType(cell) = vbString Then
> If cell.Column = 1 Or cell.Column = 7 Or cell.Column = 12 Then
> If Len(cell) > 8 Then cell = Left(cell, 8)
> End If
> End If
> Next cell
> End Sub
>
> "J.W. Aldridge" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have a web query that pulls a string of data in which I only need
> > the first 8 characters.
> >
> > Any way I can have a macro that will delete any characters to the
> > right of the 8th character in certain string/columns (multple columns
> > - A, G, & L)?
> >
> > I will run this after the data is retrieved.
> >
> > Thanx
> >
>
>
>

I was concerned about what would happen if there were numbers and blank
cells in the ranges. Sure enough, if I throw in some numbers and blank
cells, I get Type Mismatch on the r.Value = line. James

"Gary''s Student" <(E-Mail Removed)> wrote in message
news:BFCD6DFA-1C5F-4ECB-B249-(E-Mail Removed)...
> Your code is an improvement over mine. If we are REALLY careful about
> defining the range, we can eliminate all the Ifs and Fors:
>
> Sub aldridge()
> Set r1 = Range("A:A,G:G,L:L")
> Set r2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
> xlTextValues)
> Set r = Intersect(r1, r2)
> r.Value = Left(r.Value, 8)
> End Sub
>
>
> --
> Gary''s Student - gsnu2007
>
>
> "Zone" wrote:
>
>> How about this? James
>>
>> Sub Left8()
>> Dim cell As Range
>> For Each cell In ActiveSheet.UsedRange
>> If VarType(cell) = vbString Then
>> If cell.Column = 1 Or cell.Column = 7 Or cell.Column = 12
>> Then
>> If Len(cell) > 8 Then cell = Left(cell, 8)
>> End If
>> End If
>> Next cell
>> End Sub
>>
>> "J.W. Aldridge" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >I have a web query that pulls a string of data in which I only need
>> > the first 8 characters.
>> >
>> > Any way I can have a macro that will delete any characters to the
>> > right of the 8th character in certain string/columns (multple columns
>> > - A, G, & L)?
>> >
>> > I will run this after the data is retrieved.
>> >
>> > Thanx
>> >
>>
>>
>>

Thanks James. It turns out you are 100% correct. We absolutely HAVE to
examine the individual items to avoid errors. The loop is necessary in this
case.

Thanks again
--
Gary''s Student - gsnu2007


"Zone" wrote:

> I was concerned about what would happen if there were numbers and blank
> cells in the ranges. Sure enough, if I throw in some numbers and blank
> cells, I get Type Mismatch on the r.Value = line. James
>
> "Gary''s Student" <(E-Mail Removed)> wrote in message
> news:BFCD6DFA-1C5F-4ECB-B249-(E-Mail Removed)...
> > Your code is an improvement over mine. If we are REALLY careful about
> > defining the range, we can eliminate all the Ifs and Fors:
> >
> > Sub aldridge()
> > Set r1 = Range("A:A,G:G,L:L")
> > Set r2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
> > xlTextValues)
> > Set r = Intersect(r1, r2)
> > r.Value = Left(r.Value, 8)
> > End Sub
> >
> >
> > --
> > Gary''s Student - gsnu2007
> >
> >
> > "Zone" wrote:
> >
> >> How about this? James
> >>
> >> Sub Left8()
> >> Dim cell As Range
> >> For Each cell In ActiveSheet.UsedRange
> >> If VarType(cell) = vbString Then
> >> If cell.Column = 1 Or cell.Column = 7 Or cell.Column = 12
> >> Then
> >> If Len(cell) > 8 Then cell = Left(cell, 8)
> >> End If
> >> End If
> >> Next cell
> >> End Sub
> >>
> >> "J.W. Aldridge" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >I have a web query that pulls a string of data in which I only need
> >> > the first 8 characters.
> >> >
> >> > Any way I can have a macro that will delete any characters to the
> >> > right of the 8th character in certain string/columns (multple columns
> >> > - A, G, & L)?
> >> >
> >> > I will run this after the data is retrieved.
> >> >
> >> > Thanx
> >> >
> >>
> >>
> >>
>
>
>

Thanks. I do like your intersect method, though!

"Gary''s Student" <(E-Mail Removed)> wrote in message
news:794476CE-740E-417F-8C81-(E-Mail Removed)...
> Thanks James. It turns out you are 100% correct. We absolutely HAVE to
> examine the individual items to avoid errors. The loop is necessary in
> this
> case.
>
> Thanks again
> --
> Gary''s Student - gsnu2007
>
>
> "Zone" wrote:
>
>> I was concerned about what would happen if there were numbers and blank
>> cells in the ranges. Sure enough, if I throw in some numbers and blank
>> cells, I get Type Mismatch on the r.Value = line. James
>>
>> "Gary''s Student" <(E-Mail Removed)> wrote in
>> message
>> news:BFCD6DFA-1C5F-4ECB-B249-(E-Mail Removed)...
>> > Your code is an improvement over mine. If we are REALLY careful about
>> > defining the range, we can eliminate all the Ifs and Fors:
>> >
>> > Sub aldridge()
>> > Set r1 = Range("A:A,G:G,L:L")
>> > Set r2 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
>> > xlTextValues)
>> > Set r = Intersect(r1, r2)
>> > r.Value = Left(r.Value, 8)
>> > End Sub
>> >
>> >
>> > --
>> > Gary''s Student - gsnu2007
>> >
>> >
>> > "Zone" wrote:
>> >
>> >> How about this? James
>> >>
>> >> Sub Left8()
>> >> Dim cell As Range
>> >> For Each cell In ActiveSheet.UsedRange
>> >> If VarType(cell) = vbString Then
>> >> If cell.Column = 1 Or cell.Column = 7 Or cell.Column = 12
>> >> Then
>> >> If Len(cell) > 8 Then cell = Left(cell, 8)
>> >> End If
>> >> End If
>> >> Next cell
>> >> End Sub
>> >>
>> >> "J.W. Aldridge" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >I have a web query that pulls a string of data in which I only need
>> >> > the first 8 characters.
>> >> >
>> >> > Any way I can have a macro that will delete any characters to the
>> >> > right of the 8th character in certain string/columns (multple
>> >> > columns
>> >> > - A, G, & L)?
>> >> >
>> >> > I will run this after the data is retrieved.
>> >> >
>> >> > Thanx
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>