Announcement

Collapse
No announcement yet.

Find & Replace String using VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find & Replace String using VBA

    Hi Folks,

    I am hoping someone can assist me with this one. First off, here is a sample of the data I am working with (source file is a .CSV which I import as external data into a worksheet):
    1. OFFERED,1417,2004-06-30 09:00:00.000,64
    1. OFFERED,1417,2004-06-30 09:30:00.000,90
    1. OFFERED,1417,2004-06-30 10:30:00.000,105
    1. OFFERED,1417,2004-06-30 11:30:00.000,91
    1. OFFERED,1417,2004-06-30 12:00:00.000,98


    As you will see with the date/time data, it contains "09:30:00.000". This is what I need to replace using a macro, as it causes problems with a pivot-table that I use later on. I need to reformat to the format "09:30:00" using a VBA script. Changing the cell format does not seem to provide me with the solution.

    Can anyone assist me with this?

    Thanks in advance,
    Damian

  • #2
    There are a couple of methods.

    First is to record the Find/Replace function Find ".0000" Replace "". Of course the ".0000" string can't be found in any other part of the data strings. Recording this then appending this code to the existing import code should do it.


    Another method will be to loop through all imported records and extracting the data prior to the ".0000" portion of the string and all data after the indicated string.


    Regards,
    Barry
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Here is the code used to accomplish the task. Using the Find/Replace technique.

      Code:
          Cells.Replace What:=".000,", Replacement:=",", LookAt:=xlPart, _
              SearchOrder:=xlByRows, MatchCase:=False
      Regards,
      Barry

      My Favorite New Thing:
      Dynamic Named Ranges



      The alternative for
      "Press Any Key To Continue."

      and we all have one we'd like to use it on

      1. Cross Posting Etiquette
      2. Are You Here To Learn: What Have You Tried?
      3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

      Comment


      • #4
        Hi Damian

        When importing the csv file the last step gives you the option to format columns as desired. If you choose the Date and Time column and tell it to convert to a Date the data comes in with correctly. However, you will need to select the column (after the import) and format as required.

        Comment


        • #5
          Thanks for you help chaps, I'm going to go with the VBA solution. Many thanks again!!

          Comment

          Working...
          X