Breaking a cell if it contains a newline character

Question

In Excel, I want to be able to automatically break a cell into 2 or more cells if they contain a newline character (alt + Enter). How do I do this so that it'll divide the cell into new cells below that row?

Answer 1

Sub MakeTwoCellsForCellHavingLF()
Dim currentCellValue As String, LFFoundAt As Integer

currentCellValue = ActiveCell.Value
LFFoundAt = InStr(1, currentCellValue, vbLf)

If LFFoundAt <> 0 Then
    ActiveCell.Value = Left(currentCellValue, LFFoundAt - 1)
    ActiveCell.Offset(1).Value = Mid(currentCellValue, LFFoundAt + 1)
End If
End Sub

Answer 2

Assume your data is in A1.

A2 should contain (Please excuse and delete the C-style comments.):

=FIND(CHAR(10),A1) // Location of CHAR(10), your newline.

ASCII 10 means newline. Hide Row 2.

A3 should contain:

=IF(
    NOT(ISERR(A2)), // Make sure there is a newline
    LEFT(A1, A2-1), // Everything up to the newline
    A1              // If there's no newline, original string
   )

A4 should contain:

=IF(
    NOT(ISERR(A2)),        // Make sure there is a newline
    RIGHT(A1, LEN(A1)-A2), // Everything after the newline
    ""                     // If there's no newline, nothing
   )