Strip data in string in column
I have the following data in column B. I need the string of data to be stripped and only have the month and year within the cell in the same column.
Thu Oct 28 23:00:12 2013
Thu Nov 28 23:15:05 2013
Thu Oct 28 23:00:04 2013
Thu Dec 28 10:02:26 2013
Thu Dec 28 19:39:00 2013
Thu Oct 28 22:15:11 2013
Fri Nov 1 06:01:28 2013
Thu Aug 28 19:13:14 2013
Fri Aug 1 03:04:59 2013
It should look like this;
Oct 2013
Nov 2013
Oct 2013
Dec 2013
Dec 2013
Oct 2013
Nov 2013
Aug 2013
I got this VBA Macro from an earlier EE posting thats just strips off the month, but I would now like the month and year:
Sub doMacro()
Dim DataRange As Range, C As Range
Set DataRange = Range("A1:A16")
For Each C In DataRange
Cells(C.Row, 2) = Split(C.Value, " ")(1)
Next C
End Sub
Thanks
Thu Oct 28 23:00:12 2013
Thu Nov 28 23:15:05 2013
Thu Oct 28 23:00:04 2013
Thu Dec 28 10:02:26 2013
Thu Dec 28 19:39:00 2013
Thu Oct 28 22:15:11 2013
Fri Nov 1 06:01:28 2013
Thu Aug 28 19:13:14 2013
Fri Aug 1 03:04:59 2013
It should look like this;
Oct 2013
Nov 2013
Oct 2013
Dec 2013
Dec 2013
Oct 2013
Nov 2013
Aug 2013
I got this VBA Macro from an earlier EE posting thats just strips off the month, but I would now like the month and year:
Sub doMacro()
Dim DataRange As Range, C As Range
Set DataRange = Range("A1:A16")
For Each C In DataRange
Cells(C.Row, 2) = Split(C.Value, " ")(1)
Next C
End Sub
Thanks
Last Comment
Sorry
Cells(C.Row, 2) = "'" & Mid(Cells(C.Row, 1), 5, 4) & Right(Cells(C.Row, 1), 4)
Cells(C.Row, 2) = "'" & Mid(Cells(C.Row, 1), 5, 4) & Right(Cells(C.Row, 1), 4)
Or simply
Cells(C.Row, 2) = "'" & Mid(C, 5, 4) & Right(C, 4)
Cells(C.Row, 2) = "'" & Mid(C, 5, 4) & Right(C, 4)
Something like this?
Sub doMacro()
Dim DataRange As Range, C As Range
Set DataRange = Range("A1:A16")
Columns("B:B").Select
Selection.NumberFormat = "@"
For Each Cell In DataRange
Dim arr As Variant
If Cell.Value <> "" Then
arr = Split(Cell.Value, " ")
Cells(Cell.Row, Cell.Column + 1) = arr(1) & " " & arr(4)
End If
Next Cell
End Sub
Sub doMacro()
Dim DataRange As Range, C As Range
Set DataRange = Range("A1:A16")
Columns("B:B").Select
Selection.NumberFormat = "@"
For Each Cell In DataRange
Dim arr As Variant
If Cell.Value <> "" Then
arr = Split(Cell.Value, " ")
Cells(Cell.Row, Cell.Column + 1) = arr(1) & " " & arr(4)
End If
Next Cell
End Sub
ASKER
this line works, but only on the first line:
Cells(C.Row, 2) = "'" & Mid(C, 5, 4) & Right(C, 4)
example output :
Oct 2013
Thu Nov 28 23:15:05 2013
Thu Oct 28 23:00:04 2013
Thu Dec 28 10:02:26 2013
Thu Dec 28 19:39:00 2013
Thu Oct 28 22:15:11 2013
Fri Nov 1 06:01:28 2013
Thu Aug 28 19:13:14 2013
Fri Aug 1 03:04:59 2013
your other solution gives me a subscript out of range error on this statement:
Cells(Cell.Row, Cell.Column + 1) = arr(1) & " " & arr(4)
Cells(C.Row, 2) = "'" & Mid(C, 5, 4) & Right(C, 4)
example output :
Oct 2013
Thu Nov 28 23:15:05 2013
Thu Oct 28 23:00:04 2013
Thu Dec 28 10:02:26 2013
Thu Dec 28 19:39:00 2013
Thu Oct 28 22:15:11 2013
Fri Nov 1 06:01:28 2013
Thu Aug 28 19:13:14 2013
Fri Aug 1 03:04:59 2013
your other solution gives me a subscript out of range error on this statement:
Cells(Cell.Row, Cell.Column + 1) = arr(1) & " " & arr(4)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Actually, I gave you the wrong macro that I was using just for stripping the month out of the string. Here it is:
Dim DataRange As Range, C As Range
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set DataRange = Range("B1:B" & LastRow)
Dim SplitArray, x As Long
For Each C In DataRange
C.Value = Split(C.Value, " ")(1)
Next C
I tried your solution with a little modifications to look for the last row of data which I didn't mention, but still it doesn't work. Here's what I'm trying:
Dim DataRange As Range, C As Range
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set DataRange = Range("B1:B" & LastRow)
Columns("B:B").Select
Selection.NumberFormat = "@"
For Each C In DataRange
Dim arr As Variant
If C.Value <> "" Then
arr = Split(C.Value, " ")
Cells(C.Row, C.Column + 1) = arr(1) & " " & arr(4)
End If
Next C
Dim DataRange As Range, C As Range
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set DataRange = Range("B1:B" & LastRow)
Dim SplitArray, x As Long
For Each C In DataRange
C.Value = Split(C.Value, " ")(1)
Next C
I tried your solution with a little modifications to look for the last row of data which I didn't mention, but still it doesn't work. Here's what I'm trying:
Dim DataRange As Range, C As Range
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set DataRange = Range("B1:B" & LastRow)
Columns("B:B").Select
Selection.NumberFormat = "@"
For Each C In DataRange
Dim arr As Variant
If C.Value <> "" Then
arr = Split(C.Value, " ")
Cells(C.Row, C.Column + 1) = arr(1) & " " & arr(4)
End If
Next C
ASKER
My error. It's working....thanks!!
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
=MID(A1,5,4)&RIGHT(A1,4)