Un-Merging Cells

[chad13]

I have cells that have multiple line items in the cell and need to separate those line items into separate cells.

For Example:

Cell B2 contains:
ABC
123
456
DEF

I need those separate line items in B2 broken out into:

C2 - ABC
C3 - 123
C4 - 456
C5 - DEF

I hope this wasn't confusing.

Thanks,

Chad

 

[QuietRiot]

what does that cell actually look like. how can i replicate this:

ABC
123
456
DEF

into 1 cell. Im trying to reproduce it on my end but cant.

 

[chad13]

Goto B2 and enter the following:

ABC<alt><enter>123<alt><enter>456<alt><enter>DEF<enter>

I believe it is a merge function.

 

[chad13]

Ok, that last post did not post right.

In B2 enter:

ABC (then hit alt-enter) 123 (alt-enter) 456 (alt-enter) DEF (alt-enter)

 

[shades]

Typically, the line break within a cell is CHAR(10) or CHAR(13). You could segment out by formula each portion based on what it is and which one is referenced.

 

[VoG]

With a macro but this is very specific:

Sub unmurge()
Dim x, myarray, i As Integer
x = Range("B2").Value
myarray = Split(x, Chr(10))
If IsArray(myarray) Then
    For i = LBound(myarray) To UBound(myarray)
        Cells(2, i + 2).Value = myarray(i)
    Next i
End If
End Sub

 

[shades]

For the first three letters in C2,

=LEFT(B2,3)

For the last three letters in C5,

=RIGHT(B2,3)

The other two would have to involve stripping away what is in C2 and C5 and applying the formulas, accounting for the CHAR(10), which is the equivalent of ALT + ENTER

So, in C3 use MID

=MID(B2,5,3)

in C4, use

=MID(B2,9,3)
Obviously, this assumes that there will always be three letters/numbers in each set. If not, then the formula gets more complicated.