Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Find & Replace String using VBA

  1. #1
    Join Date
    11th April 2003
    Location
    South Scotland
    Posts
    120

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,761
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,761
    Here is the code used to accomplish the task. Using the Find/Replace technique.

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697
    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.

  5. #5
    Join Date
    11th April 2003
    Location
    South Scotland
    Posts
    120
    Thanks for you help chaps, I'm going to go with the VBA solution. Many thanks again!!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: January 23rd, 2008, 02:16
  2. Replace Second Word Of A String
    By Upside in forum EXCEL HELP
    Replies: 3
    Last Post: December 9th, 2007, 12:01
  3. Replace String Within a String
    By kino in forum EXCEL HELP
    Replies: 5
    Last Post: August 24th, 2007, 15:25
  4. Replace part of a string
    By badger55 in forum EXCEL HELP
    Replies: 5
    Last Post: February 21st, 2006, 00:21
  5. using replace in string value
    By boromuse in forum Excel and/or Access Help
    Replies: 15
    Last Post: February 6th, 2006, 22:35

Bookmarks

Posting Permissions

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