Access Excel remove line break and carriage return

This Access / Excel tutorial explains how to remove carriage return and remove line break in a text.

You may also want to read:

Excel VBA separate line break data into different rows

Access Excel remove carriage return and remove line break

Recently our company uploaded employee data in spreadsheet data to the new system. One user reported that her home address shows some special code (something like hex code) that is supposed to be a space. Since the code appears after a comma, I suspected that it was a carriage return so I did a google serach which confirmed my theory.

I look at our original spreadsheet data, the address shows in one line, but it shows two lines in the formula bar. The user said the data was generated from another system.

Excel Remove carriage return and remove line break

There are several ways to change line in a Cell:

1) carriage return – Char(13) or vbCr

2) line feed – Char(10) or vbLf

3) carriage return + line feed – Char(13)& Char(10) or vbCrLf

You can directly use Substitute Function to replace line break with a space or with nothing.

Assume that you want to remove line break in Cell A2, use the below formula in an assisted column

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10)," "),CHAR(13)," ")

Alternatively, you can use VBA to perform the same task. Note that VBA Replace Function is similar to Substitute Function in Worksheet.

Public Function wRemoveLineBreak(str)
    wRemoveLineBreak = Replace(Replace(Replace(str, vbLf, " "), vbCr, " "), vbNewLine, " ")
End Function

Access Remove carriage return and remove line break

The Function syntax of Access Function is slightly different from Excel but the logic is the same.

Expr1: Replace(Replace(Nz([Original Address],""),Chr(10)," "),Chr(13)," ")

You can also use the same custom Function except that you need to add Null handling.

Public Function wRemoveLineBreak(str)
    If Not IsNull(str) Then
        wRemoveLineBreak = Replace(Replace(Replace(str, vbLf, " "), vbCr, " "), vbNewLine, " ")
    Else
        wRemoveLineBreak = str
    End If
End Function

Outbound References

http://stackoverflow.com/questions/10024402/how-to-remove-line-break-in-string

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *