# I have a spreadsheet with a column with positive and negative numbers. How can I separate them into 2 separate columns?

I have a spreadsheet with a column with rows of positive and negative numbers in no set order. I want to move all of the negative numbers to an adjacent column so that I end up with two columns, one with positive numbers in all of the rows and one with negative numbers in all of the rows. How can I do this?
###### Who is Participating?

Microsoft MVP ExcelCommented:
Hello,

here is another formula approach. Unlike the suggestion in the first comment, this approach will not leave any blank cells.

You need two helper columns that return the row number if the number in column A is positive (or negative respectively). Then you can use a formula like

=IFERROR(INDEX(\$A\$1:\$A\$45,SMALL(\$B\$2:\$B\$45,ROW(A1))),"")

to return all the positive numbers in column A. Copy down, of course. See attached for a working example. Hit F9 to refresh the numbers and see the columns update. You can hide the helper columns if you want to keep the spreadsheet tidy.

cheers, teylyn
Book1.xlsx
0

Commented:
Hi,
you can make 2 new columns.
in the first column (B) , top cell use:
=IF(A1<0,A1,"")
and in the second column (C) , top cell use:
=IF(A1>=0,A1,"")
Assumptions:
column A has the original values.
You then drag the formulas down so it fills the rest of the cells with the correct functions
0

Excel MVPCommented:
Hi,

this is a VBA solution. It assumes your values begin in A1 and are in column A. The positive numbers will go to column B and Negative to column C...

Sub PositiveAndNegative()
Dim LastRow As Long, MyRange As Range, i As Integer, j As Integer
Range("A1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row

For j = 1 To LastRow
If Range("A" & j).Value > 0 Then
Range("B" & j).Value = Range("A" & j).Value
End If
Next

For i = 1 To LastRow
If Range("A" & i).Value < 0 Then
Range("C" & i).Value = Range("A" & i).Value
End If
Next
End Sub

Have a nice day and good luck.
PosAndNeg.xlsm
0

Excel MVPCommented:
Hi,

i just modified it a bit, it now deletes all the blank cells in columns B and C and gives only the values...

If you needed the values to stay in the same row, than this will be of no value to you, but if you just needed the values, this is a much cleaner solution than the previous.

Sub PositiveAndNegative()

Dim LastRow As Long, MyRange As Range, i As Integer, j As Integer

Range("A1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row

For j = 1 To LastRow
If Range("A" & j).Value > 0 Then
Range("B" & j).Value = Range("A" & j).Value
End If
Next

For i = 1 To LastRow
If Range("A" & i).Value < 0 Then
Range("C" & i).Value = Range("A" & i).Value
End If
Next

Rng = "B1:C" & LastRow

Range(Rng).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Range("A1").Select

End Sub

Good luck...
PosAndNeg.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.