How to Split Alt Enter Column Data into Separate Rows

[AhmedExperts]

Hello Guys, I am recently working on a project, But I met some difficulty while doing it in Excel.
Here is the present Format of Data in Excel.. Column 2 has alt enter in it. I don't know how to enter image here so I created link, Please visit

Before Format

And this is the of Format What I want. Now Column 2 data must be in separate cell... Kindly Guide me how to do, I am simple user of Excel.
After Format
Please ask me if you are unable to understand.
Thanks in Advance for help.

 

[Joe4]

Welcome to the Board!

I am unable to view your images, but see if this following link helps you. It describes how you can use Text To Columns with soft carriage returns (ALT-ENTER):
http://www.mrexcel.com/forum/excel-questions/363279-text-delimited-soft-return.html#post1799038

 

[AhmedExperts]

A |B
1 00001 |mechanical
|plant
|dessert
2 00002 |Onion
|potato
Hello Thanks for quick response. above is the recent format of document with me, Column B is having ALT + Enter in it...

So that the outcome would look like this:

Code:

A |B
1 00001 |mechanical
2 00001 |plant
3 00001 |dessert
4 00002 |onion
5 00002 |potato

 

[Joe4]

I think I would first use the method I described in my first reply, then run some VBA code to restructure the data down columns instead of across rows.
Is VBA an acceptable solution for you?

 

[Joe4]

This is the VBA code that I came up with to do that:

Sub DataFormatMacro()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myLastCol As Long
    Dim myCol As Long
    Dim myInsertRow As Long
    
    Application.ScreenUpdating = False

'   Find last row in column A with data
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Split column B into multiple columns
    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="" & Chr(10) & "", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
        
'   Loop through row backwards
    For myRow = myLastRow To 1 Step -1
'       Find last column in row
        myLastCol = Cells(myRow, Columns.Count).End(xlToLeft).Column
'       If there is more than one item from column B, insert down
        If myLastCol >= 3 Then
            myInsertRow = 1
'           Loop through columns, inserting blank row, and copy values down
            For myCol = myLastCol To 3 Step -1
                Rows(myRow + myInsertRow).Insert
                Cells(myRow + myInsertRow, "A") = Cells(myRow, "A")
                Cells(myRow + myInsertRow, "B") = Cells(myRow, myCol)
                Cells(myRow, myCol).ClearContents
                myInsertRow = myInsertRow + 1
            Next myCol
        End If
    Next myRow
              
    Application.ScreenUpdating = True
              
End Sub