Remove carriage returns from .txt file

tbeards

New Member
Joined
May 27, 2009
Messages
47
I have searched all over and have not been able to find a way to solve my problem. In short, we use text files to save customer information that was created in userforms in Excel (save button on the userform will create a text file. My problem is that the original code created text files that had a delimiter, which I have since changed but there are older text files out ther that users are importing where the delimiter causes a problem.

The problem is caused by a text box that I created where the user can put in "Notes" about the customer such as credit ratings, stock symbol, etc. and they have copied and pasted from an Excel cell from another workbook where there are carriage returns in that cell (multiple lines in one cell). My current process will open up the text file from Excel and they will copy and paste the cells into my model. The old text files, if carriage returns were used, messes up my import cell references because it creates one more line per carriage return used in the original Excel sheet.

I think I can resolve this by having the user select the text file, open up a new workbook and then generate the Clean code below and then copy and paste the updated information into my model (hopefully without the user seeing anything other than selecting the text file.

For Each cl In ActiveSheet.UsedRange
cl.Value = Application.Clean(cl.Value)
Next

Any help would be appreciated. It seems like an easy task but getting frustrating.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi

Are you sure it is a carriage return (ascii character 13) and not a linefeed (ascii character 10)? Assuming it is, then the following should clean the text file in place:

Code:
Sub Clean_Text()
Dim strFile As String
Dim strBuffer As String
Dim ff As Integer

strFile = "C:\SomeFolder\MyTextFile.txt"

strBuffer = Space(FileLen(strFile))

ff = Freefile

Open strFile for Binary Access Read As #ff
  Get #ff,,strBuffer
Close #ff

strBuffer = Replace(strBuffer,Chr(13),vbNullString)

Kill strFile

Open strFile For Binary Access Write As #ff
  Put #ff,,strBuffer
Close #ff

End Sub

TRY THIS ON A COPY OF YOUR FILE FIRST!!
 
Upvote 0
Thanks for the code. I will be able to use it for other purposes; however, it did not solve my problem. If select all the text lines in my text file and then paste into an Excel worksheet, I see the multiple lines. If I go to the text file down to line where I have the problems, when I press the right arrow key, I need to press it twice where the multi lines are in Excel. If I open up the text file from Excel, it creates one row per line. I need to somehow incorporate the clean function.
 
Upvote 0
Sounds to me like you have additional linefeeds, not carriage returns - give this amended code a try (again on a test file):

Code:
Sub Clean_Text()
Dim strFile As String
Dim strBuffer As String
Dim ff As Integer

strFile = "C:\SomeFolder\MyTextFile.txt"

strBuffer = Space(FileLen(strFile))

ff = Freefile

Open strFile for Binary Access Read As #ff
  Get #ff,,strBuffer
Close #ff

strBuffer = Replace(Replace(strBuffer,Chr(13),vbNullString),Chr(10) & Chr(10),Chr(10))

Kill strFile

Open strFile For Binary Access Write As #ff
  Put #ff,,strBuffer
Close #ff

End Sub
 
Upvote 0
Thanks for the quick responses! Didn't quite work. I generated the code and that worked great except for my problem child line. After I ran your code, I opened up the text file and pasted below. If you go to lines: "ABC Cusomter
Symbol: ABC
Ratings: BBB+/ WR
CDS (bps per annum 1-5 yrs): No info. available
Public Debt: None ; Sr. Unsecured; due 6/14 ; no trades available
Bank Loan: None Term Loan; Unsecured; mat "

I need to have all of those lines on one row (no mutil lines). If you paste the text below starting with ~2009 through the end and paste into Notepad (if you use the right arrow key it will take two clicks to go to the next character) or Excel you will see that it doesn't get rid of the line returns.

~tbeards



~2009 Model V10.0
Value/Formula
Customer Information
2-256nda
ABC Customer

TRUE
FALSE
BBB-
FALSE
TRUE
FALSE
FALSE
FALSE
Sales Information
United States
US
US Southeast
Joe Smith
Competitive Information
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
N/A
FALSE
N/A
Additional Information
None
2010
0
"ABC Cusomter
Symbol: ABC
Ratings: BBB+/ WR
CDS (bps per annum 1-5 yrs): No info. available
Public Debt: None ; Sr. Unsecured; due 6/14 ; no trades available
Bank Loan: None Term Loan; Unsecured; mat "
Payment Terms/Information
30
TRUE
FALSE
10
35
Other
TRUE
FALSE
FALSE
 
Upvote 0
Whew, I got it to work with your code with a minor tweek. I changed the strBuffer variable to be strBuffer = Replace(strBuffer, Chr(10), "") and it worked beautifully!

Thank you so much for the help!

Cheers!

~tbeards
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top