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.


image 48 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube