Remove Extra Commas In Notepad from Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/vba/remove-extra-commas-in-notepad-from-microsoft-excel.html
SHARE




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:

 

img1

 

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

 

img2

 

  • 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

img3

 

Code explanation:

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

 

img4

 

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

 

img5

 

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

 

img6

 

  • 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 [email protected]

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>