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

wittonlin

Board Regular
Joined
Jan 30, 2016
Messages
144
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Hi

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

Is that waht was required, let me know.

Code:
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
 
Upvote 0
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?

Rich (BB code):
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
 
Last edited:
Upvote 0
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

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

this part puts them back in.

Code:
[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

Code:
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]
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top