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.

MANAGEMENT ACCOUNTING

Developing finance leaders

A good leader recognizes that part of the job is developing the next generation of leaders. Veronica McCann, CGMA, a former division CFO at Commerzbank in Singapore, shares tips on developing future finance leaders.

PROFESSIONAL ISSUES

Belicia Cespedes: A CPA at 17

Through hard work and determination, Belicia Cespedes earned the credential before she was even eligible to vote.

SPONSORED REPORT

How to audit high risk areas

Revenue recognition, internal control over financial reporting, accounting estimates and going concern are areas of audit that have emerged as particularly challenging and complex.