Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Characters | Excel Discussion (Misc queries) | |||
deleting certain numbers of characters in a cell in a column | Excel Programming | |||
Deleting characters that are not numbers | Excel Discussion (Misc queries) | |||
deleting characters | Excel Programming | |||
Deleting unwated control characters at the end in a particular column | Excel Programming |