Help : Vba to take last 4 numbers

[Revathi]

Hi all,

Can anyone help me out for the below requirement.

> I have dates in Column S wherein i want to copy only the year (which is last 4 digits) from Column S and paste it in column T.

> I need to use the used-range based on Column A. Please see below example.

[TABLE="width: 283"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Column S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Column T[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hi all,

Can anyone help me out for the below requirement.

> I have dates in Column S wherein i want to copy only the year (which is last 4 digits) from Column S and paste it in column T.

> I need to use the used-range based on Column A. Please see below example.

[TABLE="width: 283"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Column S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Column T[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]29-08-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018[/TD]
[/TR]
</tbody>[/TABLE]

Assuming your values start on Row 1, the if those dates are text values, the you can use this...

=RIGHT(S1,4)

However, if your dates are real Excel dates formatted to look like you showed us, then you would need to use this instead...

=YEAR(S1)

 

[Revathi]

Thanks for your immediate response. But i am not able to use this using vba code.. Is it possible.

 

[Peter_SSs]

Rick did mention that the dates might be text or real Excel dates (numbers). Which are they?

 

[Revathi]

Rick did mention that the dates might be text or real Excel dates (numbers). Which are they?

Hi,

Those cells are not text, that is excel dates.

Thank you..

 

[VBABEGINER]

try this..

Sub Macro1()


Dim cn As Integer, i As Integer


With Worksheets("Sheet2")
For i = 2 To cn
    cn = .Range("S2").End(xlDown).Row
    .Cells(i, "T").Value = Right(.Cells(i, "S").Value, 4)
Next i
End With


End Sub

 

[Peter_SSs]

Those cells are not text, that is excel dates..

Then try

Sub Extract_Year()
  With Range("S2:S" & Range("A" & Rows.Count).End(xlUp).Row)
    .Offset(, 1).Value = Evaluate(Replace("if(#="""","""",Year(#))", "#", .Address))
  End With
End Sub