Video: How to fix trailing minus signs in Excel with ASAP Utilities

The following video will show you how to how to fix trailing minus signs in Excel by using ASAP Utilities. This can be useful if you have imported data from a system that reports negative numbers with a trailing minus.

If you've ever worked with imported numbers you might have faced the problem that negative numbers have the minus at the right of the number in stead of on the left. For example it shows 150- in stead of -150. This especially can occur if you import or download data from financial systems. With the minus sign at the right Excel will not recognize the number as a negative number and you cannot use it in your calculations.

There are a few approaches to solve this. In Excel 2002 and 2003 you can specify this in Step 3 of the Text Import Wizard when you click on the "Advanced button" (the option "Trailing minus sign for negative numbers").
You can also use a worksheet function to convert the numbers in an extra column:
=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)

Another easy and fast approach is to use ASAP Utilities to fix the numbers. Just select the cells and then in the Excel menu choose ASAP Utilities » Numbers » Move "-" from back to front (e.g. "65-" to "-65")

Numbers before:
Numbers with a trailing minus sign
Numbers after:
Numbers converted to normal negative numbers you can use in your calculations

Note: This old video is no longer available and will in 2021 be replaced with a new version in a modern version of Excel.

5 comments

Kanwaljit Singh Dhunna

Hi Bastien,

May the couple live 1000 years.

I Opened two instances of Excel 2007. While the first opened smoothly, the second gave a strange message like as follows.

"ASAP Utilities ribbon.xlam is locked for editing by Kanwaljit"
"Open read only or click notify to open read only and received notification when the document is no longer in use."

I have mailed the Screen shot to you.

Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

Thank you for your email.

Are you using the latest version 4.2.1?
If not, can you please upgrade, this bug should be solved in the new version.

Kind regards,
Bastien Mensink

Kanwaljit

Hi Bastien,

May the couple live 1000 years.
I was using 4.2.1 RC3. I have now downloaded the 4.2.1 and surely it will have solved the bug.

Regards
CA Kanwaljit Singh Dhunna
India

Addy

Hi Bastien,

Is there any function or way to do the opposite of " How to fix trailing minus signs in Excel with ASAP Utilities”

I mean suppose we wish to upload values and would like to have all numbers to be

100-
250-

Can we do it ?

Bastien

Hello Addy,

Yes you can use the following utility to add the minus sign to the end:
ASAP Utilities » Fill » Insert after current value…
//www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=80&utilities=Fill

Kind regards,
Bastien