Formula in macro

  • Thread starter Thread starter Tom Walat
  • Start date Start date
T

Tom Walat

Using Excel 2000

Formatting the text in this cell:
Friday, 02/27/04, Post Time: 8:00 p.m.

I want the text converted to:
Friday 8 p.m.

Looking for:
A macro to find the right cell, format the cell and paste the
proper results in A1. (Days of the week and the post time will change
from file to file.)

Thanks for any help in advance.

- Tom
twalat@_NOSPAM_enterprisenews.com
(Remove _NOSPAM_ for e-mail replies)
 
Hi
try the following (converts your selection)

Sub format_time()
Dim rng As Range
Dim cell As Range
Set rng = Selection
For Each cell In rng
If cell.Value <> "" Then
cell.Value = Left(cell.Value, InStr(cell.Value, ",") - 1) & " "
_
& Mid(cell.Value, InStr(cell.Value, "Post Time:") + 11, 255)
End If
Next
End Sub
 
Back
Top