Reverse the negativity

BY J. CARLTON COLLINS

Q: Our company’s old accounting system produces financial reports with trailing minus signs, which we open in Excel and fix manually by retyping the data. Is there an automated way to remove trailing minus signs and convert those numbers to negative numbers with leading minus signs?

A: Presented below are three Excel-based solutions to converting numbers with trailing minus signs to negative values.

1. Using the IF, RIGHT, and SUBSTITUTE functions. The following formula checks for a trailing minus sign (using the IF and RIGHT functions), and if a trailing minus sign is present, the formula removes the trailing minus sign (using the SUBSTITUTE function) and changes the number to a negative number by multiplying the number by negative one: =IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)

2. Using the VALUE, IF, RIGHT, LEFT, and LEN functions. As an alternative approach, this following formula checks for a trailing minus sign (using the IF and RIGHT functions), and if a trailing minus sign is present, the formula inserts the trailing minus sign in front of the number (using the RIGHT function again), removes the trailing minus sign (using the LEFT and LEN functions), and then converts the rearranged results to a number (using the VALUE function): =VALUE(IF(RIGHT(A1,1)="-",RIGHT(A1,1)&LEFT(A1,LEN(A1)-1),A1))

(Both formulas described above accomplish the same result using different formula approaches; the first approach is a little less complicated.)

3. Using Text to Columns. You can also fix trailing minus signs using the Text to Columns command, as follows:

a. Select a range of data containing numbers with trailing negative signs (note that it is OK if some of the selected data does not contain trailing minus signs);

b. From the Excel Ribbon select Data, Text to Columns;

c. In the resulting Convert Text to Columns Wizard - Step 1 of 3 dialog box, click the Next button twice, then click the Advanced button;

d. Make sure the box labeled Trailing minus for negative numbers is checked;

   
e. Click OK, Finish.

f. This operation replaces all trailing negative signs in your data selection range with leading minus signs.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.