Hi Gurus,
I have 3 individual macros that I have been using but realise that sometimes these can be problematic.
What I need to do is after importing 200-300 rows of data is to move the last part of the text (Added dd mmm yyyy) from column A into column B. Ideally I would like to have the data in column B to be actual dates so instead of "Added 17 Jan 2021" it becomes 17/01/2021 (so I can later sort on column B). The problem I have found is that the imported data isn't always 17 characters - so "Added 5 Jan 2021" is only 16 whereas "Added 21 Jan 2021" is 17. So I am stumped!
For my education I would prefer to see examples that break down the processes. Any ideas?
I have 3 individual macros that I have been using but realise that sometimes these can be problematic.
What I need to do is after importing 200-300 rows of data is to move the last part of the text (Added dd mmm yyyy) from column A into column B. Ideally I would like to have the data in column B to be actual dates so instead of "Added 17 Jan 2021" it becomes 17/01/2021 (so I can later sort on column B). The problem I have found is that the imported data isn't always 17 characters - so "Added 5 Jan 2021" is only 16 whereas "Added 21 Jan 2021" is 17. So I am stumped!
For my education I would prefer to see examples that break down the processes. Any ideas?
VBA Code:
Sub B_CopyAdded()
For Each cell In Range("A2:A2000").Cells
cell.Offset(0, 1).Value = Right(cell.Value, 17)
Next cell
Columns("A:A").Select
End Sub
Sub C_RemoveAdded()
' Deletes last 17 characters
' Defines variables
On Error Resume Next
For Each cel In Range("A2:A2000")
myVal = cel.Value
cel.Value = Left(myVal, Len(myVal) - 17)
Next cel
End Sub
Sub D_TrimCells()
Dim Cl As Range
For Each Cl In Range("B2:B2000", Range("A" & Rows.Count).End(xlUp))
Cl.Value = Trim(Cl.Value)
Next Cl
End Sub