# Convert Trailing Negative Sign

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• My manufacturing system extracts negative numbers in an unconventional way (at least for me). For instance -261 it lists it as 00000261- (for positive, it just list 261). It will import into excel (from the text file) into this format. What formula can I write to convert it to -261 when in Excel and extract the leading zeros and move the +/- sign to the front.

Thanks

• With help from another site, here are some options:

1. If you have Excel XP, there is an option under Text to Columns that will allow you to identify Trailing Minus for Negative Numbers.

2. This formula will convert your numbers:
=IF(RIGHT(A2)="-",-SUBSTITUTE(A2,"-",""),A2)

3. This macro will do the same without creating a new column of data:

Credit this one to Gord Dibben (Microsoft MVP).

• Re: Negative values and leading zeros

Hi Seti,

Thanks. Your formula is working fine.

Can't we make the code which you have given to refer to the selection instead of Column 'A', as it is in your current code.

Sorry, lost touch with excel..it's been a long time...

Thanks.

• Re: Negative values and leading zeros

Wow, a post response from 2 years ago. I don't even remember this post. However, this code should use your selection, rather than column A. Also, I am not the author of the original code.

• Re: Negative values and leading zeros

Quote from tomlon

My manufacturing system extracts negative numbers in an unconventional way (at least for me). For instance -261 it lists it as 00000261- (for positive, it just list 261). It will import into excel (from the text file) into this format. What formula can I write to convert it to -261 when in Excel and extract the leading zeros and move the +/- sign to the front.

Thanks

Custom format the cells like: +General;-General;General as adding a + for positives will make them text. To convert trailing negatives, try

=IF(RIGHT(A1)="-",SUBSTITUTE(A1,"-","")*-1,A1)

Then copy and Edit>Paste Special - Values

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!