Macro to Convert

[deepakga123]

Hi Team,

Appreciate your help here
I need macro to convert below data in column A into column B, I have huge data like this which I do manually

 

[Fluff]

How about

Sub deepakga()
   With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("text(replace(@,1,find("" "",@),"""")&year(today()),""00 0000"")", "@", .Offset(, -1).Address))
   End With
End Sub

 

[deepakga123]

How about

Sub deepakga()
   With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("text(replace(@,1,find("" "",@),"""")&year(today()),""00 0000"")", "@", .Offset(, -1).Address))
   End With
End Sub

Thank You very much Fluff,
It is working same as I wanted

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

Assuming your week numbers are 1 or 2 digits long, here is another (slightly more compact) macro that you can consider...

Sub deepakga123()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow) = Evaluate("TEXT(RIGHT(A2:A" & LastRow & ",2)&YEAR(NOW()),""00 0000"")")
End Sub

 

[deepakga123]

Assuming your week numbers are 1 or 2 digits long, here is another (slightly more compact) macro that you can consider...

Sub deepakga123()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow) = Evaluate("TEXT(RIGHT(A2:A" & LastRow & ",2)&YEAR(NOW()),""00 0000"")")
End Sub

Thanks Rick appreciate you help here, Code is working as i wanted