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
- 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 email@example.com