Edit Txt file data

[Chewyhairball]

Hi

I have thousands of lines of data comprised of the 2 types below and I would like to edit the numbers so they are to 1 decimal place.

<Test:Module>2</Test:Module> would become <Test:Module>2.0</Test:Module>
<Testb:Moduleb>4.2222</Testb:Moduleb> would become <Testb:Moduleb>4.2</Testb:Moduleb>

I currently import the data from a txt file into column A Sheet 1.
Sheet 2 has formulas in column A to do this task which I then copy and paste into a new txt file.

So my questions are

1. can I do this using VBA before copying and pasting back into a txt file and if so how.
2. Can I use vba to alter the txt file directly and if so how.

Thanks for any help with this.
Thank Rory

 

[Peter_SSs]

I have thousands of lines of data comprised of the 2 types below and I would like to edit the numbers so they are to 1 decimal place.

<Test:Module>2</Test:Module> would become <Test:Module>2.0</Test:Module>
<Testb:Moduleb>4.2222</Testb:Moduleb> would become <Testb:Moduleb>4.2</Testb:Moduleb>

When you say you want to edit to 1 decimal place, are you only talking about numbers like the blue one above or both blue and red edited to 1 decimal place?

 

[Chewyhairball]

When you say you want to edit to 1 decimal place, are you only talking about numbers like the blue one above or both blue and red edited to 1 decimal place?

Hi

Both of those numbers.

The data has many other different rows but it is just numbers sandwiched between the above that needs altered.

Thanks

Rory

 

[Chewyhairball]

Hi

Both of those numbers.

The data has many other different rows but it is just numbers sandwiched between the above that needs altered.

Thanks

Rory

as an example my formulas only change the number specific to <Test:Module> </Test:Module> and <Testb:Moduleb></Testb:Moduleb>

 

[Peter_SSs]

Both of those numbers.

Yes, I didn't read your original information very well, as you had already given that information.

as an example my formulas only change the number specific to <Test:Module> </Test:Module> and <Testb:Moduleb></Testb:Moduleb>

.. and is that all you want changed or do you want Test:List and anything else that might occur also changed?

 

[Peter_SSs]

Assuming they are all to be 1 decimal place try this. For testing, assumes data in col A and results in col B.

Sub Edit_To_1_DP()
  Dim a As Variant
  Dim i As Long, p1 As Long, p2 As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    p1 = InStr(s, ">")
    p2 = InStr(2, s, "<")
    a(i, 1) = Left(s, p1) & Format(Val(Mid(s, p1 + 1)), "0.0") & Mid(s, p2)
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

If it is just the 'Module' lines then try

Sub Edit_To_1_DP_v2()
  Dim a As Variant
  Dim i As Long, p1 As Long, p2 As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    If InStr(1, s, "Module", vbTextCompare) > 0 Then
      p1 = InStr(s, ">")
      p2 = InStr(2, s, "<")
      a(i, 1) = Left(s, p1) & Format(Val(Mid(s, p1 + 1)), "0.0") & Mid(s, p2)
    End If
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

 

[Georgiboy]

I was playing with this, I had assumed you wanted to edit the text file without ever importing it into cells in Excel.
The below checks that the word "test" and "module" exist on the line, it then edits that line so the number is formatted as "0.0".
It does so in an array and the spits the array back out into the text file. Nothing is ever imported to Excel.

Sub EditTxtFile()
    Dim fPath As String, fCont As Variant, fNum As Integer
    Dim x As Long, str As String, var As Variant

    fPath = "C:\Users\jbloggs\Desktop\test.txt" ' text file location, change to suit
    fNum = FreeFile
    Open fPath For Input As #fNum
    fCont = Split(Input(LOF(fNum), fNum), vbNewLine)
    For x = 0 To UBound(fCont)
        str = fCont(x)
        If InStr(str, "Test") > 0 And InStr(str, "Module") > 0 Then
            var = Split(Replace(str, ">", "<"), "<")
            fCont(x) = "<" & var(1) & ">" & Format(var(2), "0.0") & "<" & var(3) & ">"
        End If
    Next x
    Close #fNum
    
    Open fPath For Output As #1
    Print #fNum, Join(fCont, vbNewLine)
    Close #fNum
End Sub

 

[Chewyhairball]

Assuming they are all to be 1 decimal place try this. For testing, assumes data in col A and results in col B.

Sub Edit_To_1_DP()
  Dim a As Variant
  Dim i As Long, p1 As Long, p2 As Long
  Dim s As String
 
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    p1 = InStr(s, ">")
    p2 = InStr(2, s, "<")
    a(i, 1) = Left(s, p1) & Format(Val(Mid(s, p1 + 1)), "0.0") & Mid(s, p2)
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

If it is just the 'Module' lines then try

Sub Edit_To_1_DP_v2()
  Dim a As Variant
  Dim i As Long, p1 As Long, p2 As Long
  Dim s As String
 
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    If InStr(1, s, "Module", vbTextCompare) > 0 Then
      p1 = InStr(s, ">")
      p2 = InStr(2, s, "<")
      a(i, 1) = Left(s, p1) & Format(Val(Mid(s, p1 + 1)), "0.0") & Mid(s, p2)
    End If
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

The code for module works almost perfectly but adds an extra bit I dont need. "2</Test:Module>"
I am not sure how to alter the code to change this.

<Test:Module>2</Test:Module>

<Test:Module>2.0</Test:Module>2</Test:Module>

 

[Chewyhairball]

Yes, I didn't read your original information very well, as you had already given that information.


.. and is that all you want changed or do you want Test:List and anything else that might occur also changed?

I realised I dont need to look for 'test'. It is just "Module" and "Moduleb".

thanks

 

[Georgiboy]

You can edit my code to look for 'Module' only by removing the below bit of code:

InStr(str, "Test") > 0 And