copy (split) part of cell in one column into a new column

[wittonlin]

I have the following date/time in Column K
Mon Nov 14 20:15:14 EST 2016

I'd like to copy the first 3 parts 'Mon Nov 14' and add it to column R for all rows in a workbook with a variable file name.

Column K remains unchanged.

Thanks so much for anyone able to help!

 

[Mark80]

Hi,

Do the first 3 parts always have the same number of characters (i.e. Day = 3 characters, Month = 3 characters, Date = 2 characters?).

If so, you could use the left function.

=LEFT(cellReference,10)

Where 10 equals the number of characters you want.

Let me know if it works.

 

[wittonlin]

I guess you're right! I didn't think that the day wouldn't be i.e. 1. Instead it's 01.

Sorry to be obtuse, but I'm not sure how to get code like this to work in a vba macro?

 

[wittonlin]

Can anyone help with a vba string that will 'copy' these first 10 characters to a new column R?

 

[Mark80]

The LEFT formula also works in VBA. There are several ways you could achieve this in VBA, it depends what you want. Can you give a more detailed scenario.

 

[wittonlin]

Sure Mark! Just a basic script is needed because we're just going to Call it within other Macros. So really as long as it's independent of the file name, sheet or how many rows, just run to the first blank row. And the columns are fixed so the date/time data is always in column K. And I need to get those first 10 digits (i.e. Mon Nov 14) only into column R. Thank you!

 

[SQUIDD]

Hi

This should look through column K and put your results in column R

Is that waht was required, let me know.

Sub Transfer()
Dim LastRow As Long
Dim y As Integer
  Dim x As Integer

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "k").End(xlUp).Row
End With
y = 1
      
For x = 1 To LastRow
  range("k" & y).Select
  ActiveCell.Offset(0, 7) = Left(range("k" & y), 10)
   y = y + 1
Next
End Sub

 

[wittonlin]

Very coolSQUIDD!! Thank you!!

I know I should put this in a separate post but just hate to be a bother.

In this snippet in bold below, part of a macro to add headers, it's overwriting row 1 I know this is so easy and upset I can't figure it out).

How can I "insert" the column headers instead?

Sub AddHeaders()
Dim headers() As Variant
Dim ws As Worksheet
Dim wb As Workbook

Application.ScreenUpdating = False 'turn this off for the macro to run a little faster

Set wb = ActiveWorkbook

headers() = Array("Firstname", "Lastname", "Mobile", "Timezone", "Address", "City", "State", "Zip", "Email", "IP", "Time and Date", _
    "Gender")
For Each ws In wb.Sheets
    With ws
    .Rows(1).Value = "" 'This will clear out row 1
    For i = LBound(headers()) To UBound(headers())
        .Cells(1, 1 + i).Value = headers(i)
    Next i
    .Rows(1).Font.Bold = False
    End With
Next ws

Application.ScreenUpdating = True 'turn it back on

End Sub

For Each ws In wb.Sheets
    With ws
    .Rows(1).Value = "" 'This will clear out row 1
    For i = LBound(headers()) To UBound(headers())
        .Cells(1, 1 + i).Value = headers(i)
    Next i
    .Rows(1).Font.Bold = False
    End With
Next ws

 

[SQUIDD]

well, it looks like that particular part of the code, clears all headers and then puts them back in.
the code below i believe removes the headers

.Rows(1).Value = "" 'This will clear out row 1

this part puts them back in.

[B]For i = LBound(headers()) To UBound(headers())
        .Cells(1, 1 + i).Value = headers(i)
    Next i

unless im missing something, dont you wan this to happen, or is this a copy and paste of someone else code and you want to keep your existing headers?

if so i think you can remove all of this

headers() = Array("Firstname", "Lastname", "Mobile", "Timezone", "Address", "City", "State", "Zip", "Email", "IP", "Time and Date", _
    "Gender")
[B]For Each ws In wb.Sheets
    With ws
    .Rows(1).Value = "" 'This will clear out row 1
    For i = LBound(headers()) To UBound(headers())
        .Cells(1, 1 + i).Value = headers(i)
    Next i
    .Rows(1).Font.Bold = False
    End With[/B]
Next ws

[/B]

 

[wittonlin]

Yes this is code found. I know it does more than what I need, but I do need the value array. The files have no headers at all, and does have a contact in row 1. So I'm trying to get this to work by "inserting" the value array rather than overwrite row 1.