Remove Extra Commas In Notepad from Microsoft Excel

While importing text file into excel spreadsheet, unfortunately, it has kept the unwanted commas at the end. It becomes really difficult to remove extra commas in the cells. If you are trying to fix this manually, then it will take a lot of time. However, it will be a lot easier with macro code.

In this article we will focus on how to remove unwanted commas & kept where it is require. It is always better to start with cleaning your data in order to avoid any error at later stage & make reports which otherwise could have been very hectic & no one wants to spend their time on re-work.

If you find any unnecessary commas in data then you can get them removed, owing to various functions, like TRIM, SUBSTITUTE, FIND, LEN, REPLACE or you can use FIND & REPLACE (CTRL + H). You can choose from several methods to remove them.

In this article, we will focus on how to remove unwanted commas from imported CSV files & then save after cleaning is completed via VBA code.

Question: Please refer to sample txt file in attachment. I need a vb code to delete the last 8 characters of line#5

For more information on question you can click on this link

Following is the snapshot of notepad file:

Line # 5 has the following text in "RX408282,20150630,,,,,,,,"

while it needs to be "RX408282,20150630" & at the same time VBA code should not delete the comma in the above 4 lines.

To get the code, we need to follow the below steps to launch VB editor:

Click on Developer tab
From Code group, select Visual Basic

Copy the below code in the standard module

Sub test()
    Dim fn As String, txt As String
    fn = Application.GetOpenFilename("TextFiles,*.txt")
    If fn = "" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    With CreateObject("VBScript.RegExp")
        .Global = True: .MultiLine = True
        .Pattern = ",+$"
        Open Replace(fn, ".txt", "_Clean.txt") For Output As #1
            Print #1, .Replace(txt, "")
        Close #1
    End With
End Sub

Code explanation:

The above code will first ask you to select the file where it is located

After the code runs, we get an output file with name Test_Clean

To check whether the code is working fine or not; let us open the .txt file & view the results

We can see the last line i.e. line # 5, which previously had unwanted commas at the end has been fixed now

Conclusion: In this way, we can remove extra commas from a specified folder & then save the correct data in another notepad file.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Suneel D'Souza
June 11, 2020 at 1:43 am

Hi the below code was very useful, however are you able to advise how i could use this to run on all the files in a folder without having to run it manually one by one i.e. loop through all files in a folder. Also I would like to add the folder name in the code rather than selecting it while the code is running. Any advise will be well appreciated.

Sub test()
Dim fn As String, txt As String
fn = Application.GetOpenFilename("TextFiles,*.txt")
If fn = "" Then Exit Sub
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
With CreateObject("VBScript.RegExp")
.Global = True: .MultiLine = True
.Pattern = ",+$"
Open Replace(fn, ".txt", "_Clean.txt") For Output As #1
Print #1, .Replace(txt, "")
Close #1
End With
End Sub

Reply ↓