Need help in Timestamp format conversion

[moramramesh]

I have timestamp under Column A in this format: 2019-06-27T16:05-0400. I want only 16:05 from that timestamp i.e. hh:mm. Can someone help how we can do using macro?

Source
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]2019-06-27T16:05-0400[/TD]
[/TR]
[TR]
[TD]2019-06-27T16:10-0400[/TD]
[/TR]
[TR]
[TD]2019-06-27T16:35-0400[/TD]
[/TR]
</tbody>[/TABLE]

Target:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]16:05[/TD]
[/TR]
[TR]
[TD]16:10[/TD]
[/TR]
[TR]
[TD]16:35[/TD]
[/TR]
</tbody>[/TABLE]

 

[patni]

Hey moramramesh,

select your cell , right click, format cells, custom, select hh:mm

 

[moramramesh]

I need to do it using macro not manually because I have huge amount of such data.

 

[patni]

please share your timestamp vba code .

 

[Fluff]

How about

Sub moramramesh()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(isnumber(search(""t"",@)),mid(@,search(""t"",@)+1,5)+0)", "@", .Address))
      .NumberFormat = "hh:mm"
   End With
End Sub

 

[moramramesh]

Thank you very much Fluff, your macro is working perfectly fine.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rick Rothstein]

How about

Sub moramramesh()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("[B][COLOR="#0000FF"]if(isnumber(search(""t"",@))[/COLOR][/B],mid(@,[B][COLOR="#FF0000"]search(""t"",@)+1[/COLOR][/B],5)+0)", "@", .Address))
      .NumberFormat = "hh:mm"
   End With
End Sub

The text appears to be of consistent structure and length so I think you can replace what I show in red above with the number 12. Also, since you are starting with cell A1, do you really need to test if there is a "t" in the cell values (especially since you do not provide a value if FALSE)?

 

[Rick Rothstein]

I think this macro should also work...

Sub moramramesh2()
  [A:A].Replace "*t", "", xlPart, , False, , False, False
  [A:A].Replace "-*", "", xlPart
End Sub