VBA to delete text before dash

[pltaine]

I am trying to trim a lot of data from the form "10am-6pm" and "11.30am-9.30pm" to "6pm" and "9.30pm".

I basically just need the finish time (I already have a macro sorted to trim for the start times). However I cannot work out why my current code isn't working.

So far I have:

Sub LeaveFinishTime()
Dim c As Range
For Each c In Range("RotaSpaceFinish")
If InStr(c.Value, "-") > 0 Then
c.Value = Right(c.Value, (Len(c.Value) - Left(c.Value, InStr(c.Value, "-"))))
End If
Next c

End Sub

I think the logic I have written is to show characters starting from the right, as many characters as the total cell contents minus the number of characters up to and including the dash, starting from the right.

Could someone point out the mistake I have here?

Thanks

 

[sheetspread]

ctrl-h replace *- with nothing works, can be recorded in a macro.

 

[mikerickson]

Have you looked at TextToColumns?

 

[Fluff]

How about

Sub LeaveFinishTime()
   Dim c As Range
   For Each c In Range("RotaSpaceFinish")
      If InStr(c.Value, "-") > 0 Then c.Value = Split(c.Value, "-")(1)
   Next c
End Sub

 

[pltaine]

How about

Sub LeaveFinishTime()
   Dim c As Range
   For Each c In Range("RotaSpaceFinish")
      If InStr(c.Value, "-") > 0 Then c.Value = Split(c.Value, "-")(1)
   Next c
End Sub

Thanks! This worked very well. What does the 2nd bracket "(1)" mean?

 

[Scott Huish]

It's the 2nd value in the array. Split starts the arrays at index 0.