How to fix/convert trailing negative signs to real numbers in Excel?

In this article, the users understand “How to fix/convert the trailing negative signs to real number in excel.” The problem is that Excel does not understand negative values. Excel does not perceive some cells as a negative number if you are adding up some cells and it seems to disregard those cells. There are several possible causes for this, but the main one is that Excel misinterprets your reference to a number. For a variety of causes, Excel cells may contain trailing negative signs that need to be fixed.

There some solution to fix/convert trailing negative signs are as follows:

• With trailing negative signs in cell with the help of formulas.

• With text to columns function.

Let us explore the article with the few examples

Example 1

Step 1

Consider a worksheet a range of cell B1:B15 which has numerals with a negative sign trailing after them. To eliminating the -ve signs from left to right, one can use the formulas below to correct them.

Formula 1: =IF (RIGHT (B1, 1) ="-", SUBSTITUTE (B1,"-","")*-1, B1)

Users would use the IF function which enables users to analyses the comparison among values logically. So, there are two outcomes that can come from an IF sentence. If your comparison is True, the first outcome will be displayed; if it is False, the second. Right function helps in the removal of Sign, working beginning from the far right to the left.

Step 2

Clicking on empty cell C1 and put the formula =IF (RIGHT (B1, 1) ="-", SUBSTITUTE (B1,"-","")*-1, B1) which is highlighted in below image:

Step 3

Copy the formula and paste into the cell C2:C15 or drag the cell C1 till cell C15. The resulted screen after (putting formula) copying or dragging cell B1 as shown below:

Example 2

A logical function known as IF tests a given condition and gives a different value if the test result is TRUE or FALSE.

Step 1

Clicking on empty cell C1 and put the formula =VALUE(IF(RIGHT(B1,1)="-",RIGHT(B1,1)&LEFT(B1,LEN(B1)-1),B1)) which is highlighted in below image:

Step 2

Copy the formula and paste into the cell C2:C15 or drag the cell C1 till cell C15. The resulted screen after (putting formula) copying or dragging cell B1 is illustrated below:

Approach 2: By using the function named Text to column

Additionally, the following procedures can be used with Text to Columns to correct trailing negative signs:

Step 1

Choose the cell named range B1:B15 to fix the lingering -ve signs which is highlighted below:

Step 2

Clicking on Data Tab under the Data tools group and click on Text to Columns which is highlighted in below image:

Step 3

After clicking on Text to columns option a dialog box is displayed and then click on Next button which is highlighted in below image:

Step 4

After clicking on Next button, another dialog box would be opened as depicted in below image:

Step 5

In the opened dialog box, again press the Next button as highlighted below:

Step 6

Click on Advanced…. Button which is highlighted in below image:

Step 7

After clicking on Advanced… button an Advanced Text Import Settings dialog box is appear which is shown below:

Step 8

Now check on Trailing minus for negative numbers check box option, which is highlighted in below image:

Step 9

Now clicking on OK Button which is highlighted in below image:

Step 10

After clicking on OK button, another dialog box would appear as shown below:

Step 11

Click on Finish Button which is highlighted in below image:

Step 12

After click on Finish button likewise, all the negative trailing signs will be fixed, which is shown below:

Conclusion

In this article, we specify the two simple approaches are explained to resolve the problem statement. The different examples, along with the step-by-step explanation, are illustrated to perform operations. The unexpected result may occur if the syntax of the if function is not written properly. With the Leading space and trailing negative sign, no space between characters may obstruct the constructive intent of the users.

Note: There is one more way to fix trailing negative signs in Excel by Using Kutools tool.