Formula in macro

[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)

 

[Frank Kabel]

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

 

[Tom Walat]

Frank:
Perfect! Thank you.
- Tom