Editing part of a string

[Aussiebear]

Over the last few months I've been compiling a workbook detailing where a specific geographical location is within the Great Barrier Reef Marine Park Authority. In column F I have numerous entries based on the following format Dist, heading from Port. After reviewing the current 7000 row data i realise that I have entered some data incorrectly and now need a method to correct the data.

Example, String reads: "113km, 45° from Bowen Marina." After talking to locals they indicate that the location should be referred to as Bowen Harbour. How can I find each occasion where the string ends in "Bowen Marina" and change that to "Bowen Harbour". I dont want to talk about "Townsville ", Townsville Breakwater Marina" or "Townsville's Marina" because I stuffed that up as well. Just hopeing someone could show me a good example and I'll do the rest.

[snb]

Sub M_snb()
activesheet.columns(6).replace "Bowen Marina", "Bowen Harbour",2
End Sub

You could have saved the trouble using a validation list containing all valid port names.
The string should have been split into 2 parts: the geographical part and the port name.

The Townsville item could be addressed by:

Sub M_snb()
activesheet.columns(6).replace "Townville's", "Townsville",2
End Sub

[Aussiebear]

Thank you snb