kcrichar

I thought I'd contribute to the masses since I run accross this from time to time. I needed to figure out what was happening within Excel and between Excel and other apps regarding carriage returns and line feeds.

Excel recognizes all variations of Chr(10), Chr(13), vbCr, vbLf, vbCrLf, and vbNewLine. How it displays them in a spreadsheet varies. For instance; a Chr(13) shows up as a square box in both the cell value and the filtered Data list but it doesn't act like a carriage return. It just acts like a nonprintable character. Chr(10) on the other hand doesn't show up as a square box in the cell view but it does show up as a square box in the filtered Data list. It does, however, behave like you'd expect a cariage return to behave.

My results tell me that in Excel:
Chr(13) = Chr$(13) = vbCr
If you need to manipulate these, say remove and replace the carriage return with something else you only need to search for either of the 3 and you will find all. That is if you search for Chr(13) and replace it with some other string you will also find and replace Chr$(13) and vbCr.

The same is true for:
Chr(10) = Chr$(10) = vbLf

Then
vbNewLine = vbCrLf = Chr(13)Chr(10) = Chr$(13)Chr$(10) and so on.
Thus if you search to replace Chr(10) with "nothing" where vbNewLine was used the results are: Chr(13) because the use of the const vbNewLine in actuality is the combination of Chr(13) concatenated with Chr(10).

Only the equivelent uses of Chr(10) will result in a cariage return behavior in Excel cell values.

I ran the following macro so I could see what was happening.

Sub TestCr()
Range("A1") = "VBA Code"
Range("B1") = "VBA Descrption"
Range("C1") = "VBA Code Results" 'put "=code("A#")" for each line to see the ASCI binary value.
Range("D1") = "Length" 'put "=Len("A#")" for each line to see how many characters the results are.

Range("A2") = vbCr
Range("A3") = vbLf
Range("A4") = vbCrLf
Range("A5") = vbNewLine
Range("A6") = Chr(13)
Range("A7") = Chr$(13)
Range("A8") = Chr(10)
Range("A9") = Chr$(10)

Range("B2") = "vbCr"
Range("B3") = "vbLf"
Range("B4") = "vbCrLf"
Range("B5") = "vbNewLine"
Range("B6") = "Chr(13)"
Range("B7") = "Chr$(13)"
Range("B8") = "Chr(10)"
Range("B9") = "Chr$(10)"

'What VBA code matches with Chr(13)
Range("E2") = Replace(Range("A2").Value, Chr(13), "Chr(13)")
Range("E3") = Replace(Range("A3").Value, Chr(13), "Chr(13)")
Range("E4") = Replace(Range("A4").Value, Chr(13), "Chr(13)")
Range("E5") = Replace(Range("A5").Value, Chr(13), "Chr(13)")
Range("E6") = Replace(Range("A6").Value, Chr(13), "Chr(13)")
Range("E7") = Replace(Range("A7").Value, Chr(13), "Chr(13)")
Range("E8") = Replace(Range("A8").Value, Chr(13), "Chr(13)")
Range("E9") = Replace(Range("A9").Value, Chr(13), "Chr(13)")

'What VBA code matches with Chr$(13)
Range("F2") = Replace(Range("A2").Value, Chr$(13), "Chr$(13)")
Range("F3") = Replace(Range("A3").Value, Chr$(13), "Chr$(13)")
Range("F4") = Replace(Range("A4").Value, Chr$(13), "Chr$(13)")
Range("F5") = Replace(Range("A5").Value, Chr$(13), "Chr$(13)")
Range("F6") = Replace(Range("A6").Value, Chr$(13), "Chr$(13)")
Range("F7") = Replace(Range("A7").Value, Chr$(13), "Chr$(13)")
Range("F8") = Replace(Range("A8").Value, Chr$(13), "Chr$(13)")
Range("F9") = Replace(Range("A9").Value, Chr$(13), "Chr$(13)")

'What...Chr(10)
Range("G2") = Replace(Range("A2").Value, Chr(10), "Chr(10)")
Range("G3") = Replace(Range("A3").Value, Chr(10), "Chr(10)")
Range("G4") = Replace(Range("A4").Value, Chr(10), "Chr(10)")
Range("G5") = Replace(Range("A5").Value, Chr(10), "Chr(10)")
Range("G6") = Replace(Range("A6").Value, Chr(10), "Chr(10)")
Range("G7") = Replace(Range("A7").Value, Chr(10), "Chr(10)")
Range("G8") = Replace(Range("A8").Value, Chr(10), "Chr(10)")
Range("G9") = Replace(Range("A9").Value, Chr(10), "Chr(10)")

'What...Chr$(10)
Range("H2") = Replace(Range("A2").Value, Chr$(10), "Chr$(10)")
Range("H3") = Replace(Range("A3").Value, Chr$(10), "Chr$(10)")
Range("H4") = Replace(Range("A4").Value, Chr$(10), "Chr$(10)")
Range("H5") = Replace(Range("A5").Value, Chr$(10), "Chr$(10)")
Range("H6") = Replace(Range("A6").Value, Chr$(10), "Chr$(10)")
Range("H7") = Replace(Range("A7").Value, Chr$(10), "Chr$(10)")
Range("H8") = Replace(Range("A8").Value, Chr$(10), "Chr$(10)")
Range("H9") = Replace(Range("A9").Value, Chr$(10), "Chr$(10)")

I hope someone is able to benefit from this.

shg

Thanks for posting.

The functions like Chr, Dir, Left, Right, Format, ... all return Variants with subtype String. They have equivalents Chr$, Dir$, Left$, ... that return Strings, and are faster if you are returning the result to a string variable. They are otherwise identical.