Excel Text to row

[gavink]

Hi

Require excel formula to convert column D order no to each and new row.

Input is "From" worksheet to output " To" worksheet.

 

[mrshl9898]

Hi,

Do you have some examples? What is being converted into what.

 

[gavink]

Hi mrshI9898

Pls refer pictures enclosed.

 

[mrshl9898]

Thanks, try this.

Sub Macro1()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim rownum As Long
Dim rownum2 As Long
Dim colnum As Long

rownum = 2
rownum2 = 2
Set ws = Sheets("From")
Set ws2 = Sheets("To")

ws.Rows("1").Copy ws2.Rows("1")

    ws.Columns("D:D").Copy ws.Columns("AA:AA")
    ws.Columns("D:D").TextToColumns Destination:=ws.Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
        
Do Until ws.Cells(rownum, 1).Value = ""
    colnum = 5
        Do Until ws.Cells(rownum, colnum) = ""
            ws2.Cells(rownum2, 1) = ws.Cells(rownum, 1)
            ws2.Cells(rownum2, 2) = ws.Cells(rownum, 2)
            ws2.Cells(rownum2, 3) = ws.Cells(rownum, 3)
            ws2.Cells(rownum2, 4) = ws.Cells(rownum, colnum)
            rownum2 = rownum2 + 1
            colnum = colnum + 1
        Loop
rownum = rownum + 1
Loop

ws.Columns("AA:AA").Copy ws.Columns("D:D")
ws.Columns("E:AA").ClearContents
        
End Sub

 

[mrshl9898]

*Just realized you said formula... Apologies. Not sure of that myself, hopefully the VBA will do

 

[gavink]

*Just realized you said formula... Apologies. Not sure of that myself, hopefully the VBA will do

Hi

Require excel formula to convert column D order no to each and new row.

Input is "From" worksheet to output " To" worksheet.

Sample file link