CODEWITHSUNDEEP
excelreplacevba
skatun
skatun

Reputation: 877

Manipulate a textfile to add 2 in front of integers(remove unknown number of spaces) in excel vba

I have a fairly long file(479394 lines) which contains node and element numbers for a FEM model I am trying to combine with another model. To do this I want to add 2 to all numbers to make sure that I have unique node/element numbers when I combine the two models. This should be straight forward, however I can't figure out how to replace several spaces after each other with the number 2. It can be 3 spaces,maybe 2 maybe 5 and so on(remove unknown number of spaces). 

Sub readTextFile()
strFileName = "Z:\Prosjekt\32637\FEM\RP_32637_PR_02_D\FEM\kim2.dat"
Open strFileName For Input As #1
dataArray = Split(input$(LOF(1), #1), vbLf)
Close #1

For Each element In dataArray

    If InStr(element, "CTETRA") Or _
        InStr(element, "PSOLID") Or _
        InStr(element, "CELAS1") Or _
        InStr(element, "         ") Or _
        InStr(element, "GRID") Then
        element = Replace(element, "remove unknown number of spaces", "2")
    End If
'write back to file
Next element

End Sub

here is a link to a stripped version (129 lines) of my input file and here is the output I want to achieve

Upvotes: 1

Views: 30

Answers (1)

omegastripes
omegastripes

Reputation: 12612

Consider the below snippet, it replaces the consecutive spaces in the string with "2":

With CreateObject("VBScript.RegExp")
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = " +"
    element = .Replace(element, "2")
End With

Upvotes: 1

Related Questions