CODEWITHSUNDEEP
excelvba
user3572141
user3572141

Reputation: 3

Extract multiple substrings with carriage return separator in excel

This forum contains everal posts on how to separate a cell in Excel into two cells, but I have a problem of having >10 names, all separated by carriage returns, which I need to split up in separate cells.

For example, a random single cell may look like this, with each name on a separate line within the cell:

LastNameA, Donald E.
LastNameB, Edward N., Jr.
LastNameC-Johnson, Susan
LastNameD, Denis A., III
LastNameE, L. Michael

etc.

Anybody an idea whether this is possible at all in Excel? I could add a graphic to further demonstrate the problem.

Upvotes: 0

Views: 436

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

try this. See inline comments for explanation

Sub SplitDemo()
    Dim rngIn As Range
    Dim rngOut As Range
    Dim NewData As Variant

    ' Get a reference to your input cell by any means you choose
    Set rngIn = [A1]

    ' Split the data on the Linefeed character
    '  this creates a 0 based, one dimensional array
    NewData = Split(rngIn.Value, vbLf)

    ' Get a reference to your output cell by any means you choose
    Set rngOut = [A2]

    ' To put split data array in a row, use
    rngOut.Resize(1, UBound(NewData) + 1).Value = NewData

    ' To put split data array in a column use
    rngOut.Resize(UBound(NewData) + 1).Value = Application.Transpose(NewData)
End Sub

Upvotes: 1

Related Questions