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

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.