Reverse the negativity


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.


News quiz: College debt, stolen identities, and retirement planning

See how much you know about these developments and others in the Journal of Accountancy news quiz.


Preventing and detecting fraud at not-for-profits

Organizations in all industries must deal with the potential for fraud to occur, and design controls to prevent and detect it. Environment, policies, and controls can help organizations steer clear of problems.


The dangers of dabbling

To meet evolving marketplace needs, CPAs often look to diversify their service offerings. Firms can mitigate the risk of experiencing competency-related professional liability claims by implementing these basic steps.