Consulting

Results 1 to 3 of 3

Thread: Editing part of a string

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location

    Editing part of a string

    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Thank you snb
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •