Find and Replace

[Mario]

I have the following script below, but I would like to have it search for
the word "hours" and replace it with a null value, so it would just display
1.25. I'm a little new to VB so I'm assuming that the "Select Case
cell.Value" is searches for the entire value of the cell rather than a value
within the cell. What would I need to do?



Sub MyReplace()
Dim cell As Range


For Each cell In Range("e1:e5000")


Select Case cell.Value


Case "1.25 hours"
cell = "1.25"

End Select
Next cell
Set cell = Nothing


End Sub

 

[Guest]

How about...
Selection.Replace What:=" hours", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I just tried to do it with the macro recorder turned on
and got the code above. This is an easy way to figure out
a lot of programming issues. Another way is to get John
Walkenbach's Excel 2002 Power Programming with VBA.
Check out...
http://www.j-walk.com/ss/books/xlbook25.htm

HTH,
Gary Brown

 

[Guest]

hi,
just a suggestion. why not copy the e column and paste in
on a new sheet then parse the "hours" out.
data>text to columns.
then you can copy the numbers and paste them back in the e
column then delete the new sheet.
yeah, crude. yeah, manual. just a suggestion.

 

[Mario]

I export my time from Outlook and calculating the time. I would like to make
it as automated as possible. Thanks for the info.