asked on # 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

Microsoft Excel

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)

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

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

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)

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

My error. It's working....thanks!!

=MID(A1,5,4)&RIGHT(A1,4)