Home > Access, Excel, SQL > Remove a line feed / carriage return from a string

Remove a line feed / carriage return from a string

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.

Categories: Access, Excel, SQL Tags: , ,
  1. Gabs
    September 14th, 2011 at 14:10 | #1

    Dear Bi-report, if you were to build a dashboard in Excel, how would you go about it?

    • September 14th, 2011 at 16:36 | #2

      Hi Gabs, I can’t really answer such a generic question as there are so many variables involved. Instead I’d like to refer you to a post on reporting theory and see if that can supply you any inspiration

      Good luck 🙂

  2. Ramya
    July 14th, 2015 at 15:57 | #3

    THANK YOU! I’ve been looking for this answer everywhere!

  1. No trackbacks yet.