I have a large amount of data that I read from a hardware device that needs to be cleaned up so it can be analyzed in Excel.
I wrote a simple find/replace VBA macro in word to convert it to .csv, but the macro takes a long time to run through large sets of data.
I'm looking for suggestions on a faster way to code this. Alternately, if there is another option for cleaning the data that would work better than Word and VBA, I'm willing to switch to it.
Sub CleanText()
Application.ScreenUpdating = False
' clean data for import to excel
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p" & "^p"
.Replacement.Text = "wvw"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = ","
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "wvw"
.Replacement.Text = "^p"
.Forward = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "$GPGGA" & "(*)" & "$GPRMC"
.Replacement.Text = "$GPRMC"
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "W" & "(*)" & "S"
.Replacement.Text = "W,"
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "V" & "(*)" & "S"
.Replacement.Text = "V,,,,,"
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "$GPRMC,"
.Replacement.Text = ""
End With
Selection.Find.Execute Replace:=wdReplaceAll
Application.ScreenUpdating = True
End Sub