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?
Neal FreedFounder & Managing PartnerAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
MarsMCommented:
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
 
Gašper KamenšekExcel 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
 
Gašper KamenšekExcel 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.

All Courses

From novice to tech pro — start learning today.