Deleting characters to the right in a column....
 

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

Deleting characters to the right in a column....
 

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

Deleting characters to the right in a column....
 

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

Deleting characters to the right in a column....
 

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

Deleting characters to the right in a column....
 

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(xlCellTypeConst ants, 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" wrote in message
ps.com...
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

Deleting characters to the right in a column....
 

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" wrote in message
...
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(xlCellTypeConst ants,
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" wrote in message
ps.com...
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

Deleting characters to the right in a column....
 

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" wrote in message
...
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(xlCellTypeConst ants,
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" wrote in message
ps.com...
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

Deleting characters to the right in a column....
 

Thanks. I do like your intersect method, though!

"Gary''s Student" wrote in message
...
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" wrote in
message
...
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(xlCellTypeConst ants,
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" wrote in message
ps.com...
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