How to detect a line break in Excel through VBA

Question

I'm trying to find the carriage return\line break in various cells, by iterating over them and running the InStr function against the text in each of those cells. The below query is finding the line break correctly in most cells, but is failing in one of those cells.

The line break in those cells was added the same for all of them, and that is by hitting Alt+Enter.

Below is the function I am using:

InStr(startlocation, text, vbLf)

I also tried all of the below, but to no avail:

InStr(startlocation, text, Chr(10)) 'this seems to be identical in results to using vbLf
InStr(startlocation, text, Chr(13)) 'No results with this
InStr(startlocation, text, ALT + 10) 'I see this returning some results sometimes, not exactly sure for which character though
InStr(startlocation, text, vbCrLf) 'No results with this

Is there any other way to represent line break so I add it to my query?

Answer 1

As others have confirmed, vbLf was indeed the correct character for identifying the line break introduced by ALT + Enter. My issue turned out to be caused by how the content of text was ending one character before the line break, therefore the InStr(startlocation, text, vbLf) on that string was not finding the line break. Extending text (which was created using the Mid function) by 1 took care of the issue. So:

Instead of: text = Mid(entryvalue, delimeterlocation, Len(entryvalue) - delimeterlocation)

I did this: text = Mid(entryvalue, delimeterlocation, Len(entryvalue) - delimeterlocation + 1)

Answer 2

The line break in cells, which is created with Alt + Enter is a vbLf.

Therefore the following should work.

InStr(startlocation, text, vbLf)

If it doesn't work that means you did something else wrong.

If you have the following data in cell A1 (Alt + Enter after 1 and 2)

Then InStr(1, Range("A1"), vbLf) returns 2.

---

For example you can use …

Dim ArrLines As Variant
ArrLines = Split(Range("A1"), vbLf)

to split these lines into an array like …

• ArrLines(0) is 1
• ArrLines(1) is 2
• ArrLines(2) is 3