Indentify line breaks in excel VBA

Question

I'm trying to identify a cell has line breaks(not the menu bar cell option, actual multiple lines through alt+enter), and extract each line separatedely

I have tried both

InStr(str, "\n") < 0

and

Split(str, "\n")

But doesn't seem to be working

Answer 1

There are no escape sequences in VBA. Use the built-in vbNewLine constant instead for the equivalent:

hasLineBreaks = InStr(str, vbNewLine) > 0

Per MSDN, vbNewline returns a Platform-specific new line character; whichever is appropriate for current platform, that is:

Chr(13) + Chr(10) [on Windows] or, on the Macintosh, Chr(13)

So you don't need to work with ASCII character codes, or even with their respective built-in constants.

Except Excel will strip CR chars from cell and shape contents, and this has nothing to do with VBA (the CR chars would be stripped all the same and "\n" wouldn't work for correctly reading that Excel data in C#, Javascript, or Python either) and everything to do with the context of where the string came from.

To read "line breaks" in a string with the CR chars stripped, you need to look for line feed chars in the string (vbLf).

But if you systematically treat the line feed character as a line ending, you'll eventually run into problems (esp.cross-platform), because ASCII 10 all by itself isn't an actual line break on either platform, and you'll find ASCII 13 characters in strings you thought you had stripped line breaks from, and they'll still properly line-break on a Mac, but not on Windows.

Answer 2

Consider either:

Split(str, Chr(10))

or

Split(str, Chr(13))

You may need to try both if the data has been imported from external source.

Answer 3

VBA is not C# ("\n"). Line breaks you'll find on: vbCr or vbLf or vbCrLf constants.

For further information, please see:
vbCr
vbLf
vbCrLf

[EDIT]

Points to Mat's Mug answer! I forgot about vbNewLine constant.