VBA to delete everything after "m" in a selection

[pltaine]

Hi guys,

I am working with a rota and there is a grid with all the data with names across Row 6 and the dates of the year down Column A. In the grid are all sorts of entries, with "Off", "Holiday" etc but all shifts are written in the form "7am-6pm". I am looking to clear all the cells of everything apart from shift start time. I have managed to write one that clears all the which aren't in the "7am-6pm" form above, but am struggling to write one which will delete everything after the first "m" (from "am").

Would really appreciate some help on this.

Cheers

 

[PatOBrien198]

In VBA, the equivalent to the Excel FIND function is InStr.

So you could use the LEFT function along with InStr to just keep the left most characters.

strStart = LEFT(strStart, InStr("am-", strStart)+1)