CODEWITHSUNDEEP
excelvbatext
Shazu
Shazu

Reputation: 587

Removing unwanted data from text file

I have a large text file exported from an application that has three unwanted zeros in each row. The text file needs to be imported into another application and the zeros cause a problem.

Basically the unwanted three zeros per row need to be deleted. These zeros are always in the same location (same number of characters when counting from the left), but the location is somewhere in the middle. I have tried various things like importing the file into excel, removing the zeroes and then exporting as text file, but always have formatting problems with the exported text file.

Can someone suggest a solution or point me in the right direction?

Upvotes: 0

Views: 780

Answers (3)

Bernard
Bernard

Reputation: 1238

curLine = Left(curLine, 104)

This will take the first 104 characters

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

something like this ? (quickly done)

Sub replaceInTx()
    Dim inFile As String, outFile As String
    Dim curLine As String

    inFile = "x:\Documents\test.txt"
    outFile = inFile & ".new.txt"

    Open inFile For Input As #1
    Open outFile For Output As #2

    Do Until EOF(1)
        Line Input #1, curLine
        Print #2, Replace(curLine, "000", "", 6, 1, vbTextCompare)
    Loop
    Close #1
    Close #2
End Sub

Alternatively, you can do that with any text editor that allows block selection (I like Notepad2, tiny, fast and portable)

Upvotes: 2

Bernard
Bernard

Reputation: 1238

I see you use excel a lot. When you import the text file into excel do you use the import function and do you push the data into separate cells? if the cell is numeric you could do the following:

=LEFT(TEXT(G5,"#"),LEN(TEXT(G5,"#"))-3)

if the cell is text:

=LEFT(G5,LEN(G5)-3)

G5 would the cell the data row/field is in.

Upvotes: 1

Related Questions