This Access / Excel tutorial explains how to remove carriage return and remove line break in a text.
You may also want to read:
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