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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.