Remove a line feed / carriage return from a string

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 Breaks 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 Breaks 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:

REPLACE([TextField],CHAR(13),"|")

Remove and Replace Line Breaks 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 Breaks 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.