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:
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
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 firstname.lastname@example.org
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.