This article describes how to remove the carriage returns and line feeds in SQL, Tableau, Excel and Access. If you ever have to deal with long strings of freetext it’s likely you’ve come across random line feed and carriage returns in that text. These unwanted characters can cause multiple problems, therefore removing or replacing them is the best option.
Remove and Replace Carriage Returns and Line Feeds in SQL
Using SQL to remove a line feed or carriage return means using the CHAR function.
A line feed is CHAR(10); a carriage return is CHAR(13).
The following code will remove linefeed characters in SQL and replace them with a zero-length string:
UPDATE TableName SET FieldName = REPLACE(FieldName,CHAR(10),'')
Remove and Replace Carriage Returns and Line Feeds in Tableau
Use CHAR(10) and CHAR(13) to remove a line feed or carriage return in Tableau
A line feed is CHAR(10) and a carriage return is CHAR(13).
Use a calculated field to replace the unwanted line feeds and carriage returns in Tableau. Create a calculated field with the following formula to replace a carriage return with a pipe character:
Remove and Replace Line Feeds and Carriage Returns in Excel
You can eliminate these characters using Find-Replace. The challenge is to find these special characters.
In the Find box hold down the Alt key and type 0 1 0 for the line feed and Alt 0 1 3 for the carriage return.
They can now be replaced with whatever you want.
Remove and Replace Carriage Returns and Line Feeds in Access
I use VBA to do to replace these characters in Access. The SQL CHAR becomes a CHR in Access.
Chr(13) is a Carriage Return
Chr(10) is a Line Feed
Sub RemoveLineFeed() Dim SQL As String 'Chr(13) = Carriage return SQL = "UPDATE TableName SET FieldName = Replace(FieldName, Chr$(10),'')" DoCmd.RunSQL SQL End Sub;
In a similar theme to this but about adding line feeds rather than removing I have another article describing how to add a line feed to an SSRS report.