+ Reply to Thread
Results 1 to 5 of 5

search within text file and replace strings with variable number of characters

Hybrid View

ingolf_ingolfsen search within text file and... 12-27-2012, 03:41 PM
JieJenn Re: search within text file... 12-27-2012, 03:49 PM
ingolf_ingolfsen Re: search within text file... 12-28-2012, 02:09 AM
OllieB Re: search within text file... 12-28-2012, 02:53 AM
jindon Re: search within text file... 12-28-2012, 03:54 AM
  1. #1
    Registered User
    Join Date
    06-01-2011
    Location
    norway
    MS-Off Ver
    Excel 2003
    Posts
    15

    search within text file and replace strings with variable number of characters

    Hi folks
    I want to do the following using VBA:
    - find some numbers in a textfile and replace these numbers.
    - I know the number of characters that shall be entered/ stored / placed into the text file
    - but the numbers that shall be replaced can be of variable numbers of characters

    The numbers that shall be replaced are in the same line as the line starting with the words "simulation results"
    After this expression there are 4 numbers separated by a space. All these 4 numbers shall be replaced with other numbers.

    For example
    The line in text file: "simulation results 0.020 923 9832.2 93.22 BDV XYXS" should be replaced with
    "simulation results 0.0666 2222 2 0 BDV XYXS"

    since numbers of characters may vary it can also look like:
    "simulation results 1 2132323.2 111.1111 11 BDV XYXS" should be replaced with
    "simulation results 0.0666 2222 2 0.213 BDV XYXS"

    I have several hundreds of these textfiles and want to change these using VBA - to give me more flexibility in my work.

    I struggle to find out these issues.

    Hope someone can guide me.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: search within text file and replace strings with variable number of characters

    won't it be

    cells.replace "simulation results 1 2132323.2 111.1111 11 BDV XYXS", "simulation results 0.0666 2222 2 0.213 BDV XYXS" ? I am just guessing

  3. #3
    Registered User
    Join Date
    06-01-2011
    Location
    norway
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: search within text file and replace strings with variable number of characters

    I guess I explained this not accurate.
    The numbers that shall be replaced are unknown. So if I could use the replace function without refering to the numbers that shall be replaced it would have worked...

    I assumed that I needed to do a search for the word "simulation results" and then search for the first space, then find somehow the first number, read the numbers of characters the first number consists of, then overwrite this number with the new number etc...

    but how? or is there another way to it?

  4. #4
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: search within text file and replace strings with variable number of characters

    Hi,

    You could use the split function with a space as a separator. This would give you an array with 8 elements whereby the numbers to be replaced are in elements 2 to 5.

    See the example code below (perhaps overkill):

    Public Sub pub_sub_ReplaceStringContents()
    
    '#
    '# declare private variables
    '#
         Dim pvt_str_Input As String
         Dim pvt_str_Output As String
         Dim pvt_var_Elements As Variant
         Dim pvt_int_ElementNumber As Integer
         
    '#
    '# initialise
    '#
         pvt_str_Input = "simulation results 1 2132323.2 111.1111 11 BDV XYXS"
         
    '#
    '# split the contents of the string - after execution of the below statement the elements 2 to 5 will always hold
    '# the numbers
    '#
         pvt_var_Elements = Split(pvt_str_Input, Chr$(32))
    
    '#
    '# change the numbers
    '#
         pvt_var_Elements(2) = 0.0666
         pvt_var_Elements(3) = 2222
         pvt_var_Elements(4) = 2
         pvt_var_Elements(5) = 0.213
    
    '#
    '# build the output string
    '#
         pvt_str_Output = vbNullString
         For pvt_int_ElementNumber = 0 To UBound(pvt_var_Elements)
              pvt_str_Output = pvt_str_Output & pvt_var_Elements(pvt_int_ElementNumber) & Chr$(32)
         Next pvt_int_ElementNumber
         
         pvt_str_Output = Trim$(pvt_str_Output)
         
    End Sub
    If you like my contribution click the star icon!

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: search within text file and replace strings with variable number of characters

    Try
    Sub test()
        Dim myDir As String, fn As String, txt As String
        Const myReplace As String = "0.0666 2222 2 0 "
        myDir = ThisWorkbook.Path & "\"  '<-- folder path
        fn = Dir(myDir & "*.txt")
        Do While fn <> ""
            txt = CreateObject("Scripting.FileSystemObject") _
            .OpenTextFile(myDir & fn).ReadAll
            With CreateObject("VBScript.RegExp")
                .Global = True
                .MultiLine = True
                .Pattern = "^(simulation results )([\d\. ]+)"
                If .test(txt) Then
                    txt = .Replace(txt, "$1" & myReplace)
                End If
            End With
            Open Replace(fn, ".txt", "_Updated.txt") For Output As #1
                Print #1, txt
            Close #1
            fn = Dir
        Loop
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1