VBA to remove certain text from CSV file

[smythcounty]

Hello, The CSV file I'm importing has annoying characters that are causing issues for my reporting. The program I export from places "-" when there is no data and I would like to remove all "-" characters. Is this possible?

Thanks

 

[JEC]

Here it is: (you have to close the textfile first)

Sub JEC()
 c00 = "C:\Users\xxx\Downloads\test.txt"
 With CreateObject("scripting.filesystemobject")
    .createtextfile(c00).write Replace(.opentextfile(c00).readall, "-", "")
 End With
End Sub

 

[Fluff]

You can also use Ctrl H to use the replace feature to replace them with nothing, or with code

ActiveSheet.UsedRange.Replace "-", "", xlWhole, , , , False, False

 

[JEC]

But not if it's a Text file ".txt"

Ctrl+H always works though

 

[Fluff]

Maybe you missed this

The CSV file I'm importing

Also the code will work work regardless of file type once it's been imported into xl.

 

[JEC]

I actually read CSV as TXT
omg

 

[smythcounty]

Here it is: (you have to close the textfile first)

Sub JEC()
 c00 = "C:\Users\xxx\Downloads\test.txt"
 With CreateObject("scripting.filesystemobject")
    .createtextfile(c00).write Replace(.opentextfile(c00).readall, "-", "")
 End With
End Sub

Thank you. I should've clarified the "-"s I need removed are located in Columns F2:FXXX and G2:GXXXX. There are some "-"s in the other columns that I wish to keep, such date separators EX. "08-24-2021".

 

[smythcounty]

Maybe you missed this

Also the code will work work regardless of file type once it's been imported into xl.

Thank you. I should've clarified the "-"s I need removed are located in Columns F2:FXXX and G2:GXXXX. There are some "-"s in the other columns that I wish to keep, such date separators EX. "08-24-2021".

 

[Joe4]

How about using the same methodology that Fluff's proposed, but this time replace ",-" with ","?
That would not replace any instances where the "-" is not the first character after each ",".

 

[Fluff]

There are some "-"s in the other columns that I wish to keep, such date separators EX. "08-24-2021".

My suggestion will only remove the - if it's the only content of the cell.
But if you only want it to work on col F & G use

ActiveSheet.Range("F:G").Replace "-", "", xlWhole, , , , False, False