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 causing you headaches when it comes to manipulating the text.

Once you know how it’s quite a simple problem to deal with. This article describes how to remove the carriage returns and line feeds in SQL, Excel and Access.

SQL

Using SQL to remove a line feed or carriage return means knowledge of the CHAR function.

A Linefeed is CHAR(10) while a carriage return is CHAR(13)

The following code will remove linefeed characters and replace them with a zero length string:

UPDATE TableName SET FieldName = REPLACE(FieldName,CHAR(10),”)

Excel

You can eliminate these characters using Find-Replace.

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

Access

I have used VBA to do this in the past. The SQL CHAR becomes a CHR in Access.

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.