Aug 03 2021 08:42 AM
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.
Aug 03 2021 09:00 AM - edited Aug 03 2021 09:01 AM
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