How to separate positive and negative numbers into two columns in Excel?
There a list of multiple values including positive and negative values in a worksheet, and you need to separate them into two columns, one contains positive values only, the other has negative values only. Now this tutorial is talking about the methods on separate the positive and negative numbers into two columns in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Separate positive numbers from the list first.
Select a blank cell and type this formula =IF($A1>=0,$A1,"") (A1 is the cell in your list), press Enter button and drag fill handle to fill range you want, you can see only positive values are separated into the column.
Then type this formula =IF($A1<0,$A1,"") into another column cell, and press Enter button, and drag fill handle to fill range you want. Now you can see the positive and negative values are separated into two columns.
If you have Kutools for Excel installed in your Excel, you can use its Select Specific Cells feature to finish the separating.
1. Select the data list and click Kutools > Select Tools > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog box, check Cell in the Selection type section, and select Greater than or equal to from the drop-down list below the Specify type, and type 0 into the box at the right of the drop-down list. See screenshot:
3. Click Ok, and only the positive values and 0 are selected, then you can copy them to one column.
4. Apply the Select Specific Cells feature again, and select Less than in the drop-down list below the Specify type, and type 0 into the text box. See screenshot:
5. Click Ok, and only the negative values are selected, then you can copy them to another column.