How To Transpose or Convert A Single Column To Multiple Columns In Excel

How To Transpose Or Convert A Single Column To Multiple Columns In Excel

There can be multiple ways to perform this trick, I will show you some of these in this tutorial now.

1st method formulas :

You can easily get your result by using one of the following formulas.:

• =IF(LEN(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1))=0,"",INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1))

• =IF(ISBLANK(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1)),"",INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1))

• =IF(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1)="","",INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1))

• =IFERROR(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1,""),"")

• =IFNA(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1,""),"")

• =IF(LEN(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1))>0,INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1),"")

• =IFERROR(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1),"")&""

• =IF(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1)="","",INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1)&"")

• =IFERROR(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1)&"","")

• =IF(ISNUMBER(INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1)),INDEX($A:$A,((ROW()-1)*3)+COLUMN()-1),"")

Note: If you want to convert the data into more columns, change the number 3 specified in the formula as per your requirement.

2nd method of doing this by VB Code:

  
  Sub movetocolumns3()
    Dim i As Long, iRow As Long
    Dim arrSource As Variant
    
    'Set the first row
    iRow = 1
    
    With ActiveWorkbook.Worksheets("Sheet1")
        'get the data into an array from the first column
        arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        
        'parse every value of the array and add the data to the next 3 columns
        For i = 1 To (UBound(arrSource) - UBound(arrSource) Mod 3) Step 3
            .Cells(iRow, 2) = arrSource(i, 1)
            .Cells(iRow, 3) = arrSource(i + 1, 1)
            .Cells(iRow, 4) = arrSource(i + 2, 1)
            iRow = iRow + 1
        Next i
        
        'add any remaining values
        Select Case UBound(arrSource) Mod 3
            Case 1 'one item to add
                .Cells(iRow, 2) = arrSource(i, 1)
            Case 2 'two items to add
                .Cells(iRow, 2) = arrSource(i, 1)
                .Cells(iRow, 3) = arrSource(i + 1, 1)
            Case Else 'nothing to add
        End Select
    End With
End Sub
      

Now go back to the excel sheet and from the view tab, click on view macros and run the macro called “movetocolumns” That’s it friends.