Breaking a cell if it contains a newline character

Tag: excel Author: wumujusha Date: 2009-08-01

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?

I won't be able to help regardless, but for people who know Excel, it might help if you specify how you are interfacing with it. COM?

Other Answer1

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

Other Answer2

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
   )