Forum Discussion

evilgreenred's avatar
evilgreenred
Copper Contributor
Aug 03, 2021

Separating one column into two column excel formatting

Hi, anyone is able to help me whether how can I separating the numbers into the left column and words will be in the right column. I had attached an excel file with a single column. My output will be shown on the right side of excel with 2 columns and words/numbers separated. 

excel

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 03, 2021

    evilgreenred 

    Run the following macro. You can change the output column by changing the value of TargetCol in the code.

     

    Sub SplitData()
    Const SourceCol = 2 ' column B
    Const TargetCol = 4 ' column D
    Dim SourceRow As Long
    Dim LastRow As Long
    Dim TargetRow As Long
    Dim LineNumber As Long
    Application.ScreenUpdating = False
    TargetRow = 1
    Cells(TargetRow, TargetCol).Resize(1, 2).Value = Array("Number", "Solution")
    LastRow = Cells(Rows.Count, SourceCol).End(xlUp).Row
    For SourceRow = 2 To LastRow
        If IsNumeric(Cells(SourceRow, SourceCol).Value) Then
            LineNumber = Cells(SourceRow, SourceCol).Value
        Else
            TargetRow = TargetRow + 1
            Cells(TargetRow, TargetCol).Resize(1, 2).Value = Array(LineNumber, Cells(SourceRow, SourceCol).Value)
        End If
    Next SourceRow
    Application.ScreenUpdating = True
End Sub

     

Resources