There are a few ways you can remove a negative sign from numbers in Excel. In this lesson, I’m going to show you three of them.
Let’s use the following example.
Here, you have a list of negative and positive numbers. We will try to make remove all minus signs.
Remove negative sign with the ABS function
The first way to remove a negative sign is by using the ABS function. This function will return the absolute value of a number. In other words, It will remove the minus sign if the value is negative, and do nothing if the value is positive.
To do this, type =ABS(A1) into cell A7. Next, autofill cells to D7.
Autofill cells to D11.
Now, you have a copy of the cells without negative values.
Replace negative sign
If you don’t need to keep the original values, you can use a feature called Replace.
You can find it under Home >> Editing >> Find & Select >> Replace or just by pressing Ctrl + H.
First, you need to select all cells with numbers and then use Ctrl + H.
In Find what, place “-“, and don’t type anything in Replace with.
Click the Replace All button.
After you do this, you will find that all minus signs are removed.
Remove negative sign with a macro
The recorder macro will look like this.
Sub RemoveNegativeSign() Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub
It will replace negative sign (What:=”-“) with nothing (Replacement:=””), therefore removing minus from all selected cells (Selection.Replace).