# Changing Position of the Negative Sign

This is a discussion on Changing Position of the Negative Sign within the Excel Questions forums, part of the Question Forums category; I have loaded a report from our system into Excel, and the negative signs are at the end of the ...

1. I have loaded a report from our system into Excel, and the negative signs are at the end of the number (i.e. 35415.01-). How can I remove the negative sign and place it in the front so that Excel knows this is a negative number. The problem is that these numbers are in columns with whole positive numbers in between. I know how to replace/remove the dash, but what I need is some sort of "GoTo" function to find the numbers with the dash, then remove the dash, then put a dash at the beginning of the number. Any suggestions?

2. On 2002-04-27 09:51, Brian from Maui wrote:
Jeff

Try this thread

http://www.mrexcel.com/board/viewtop...c=1455&forum=2
Also you can use ASAP-utilities, free downloadable at:

http://asap-utilities.com

which has an option under Numbers for moving - from back to front.

Aladin

3. On 2002-04-27 09:38, Jeff Steele wrote:
I have loaded a report from our system into Excel, and the negative signs are at the end of the number (i.e. 35415.01-). How can I remove the negative sign and place it in the front so that Excel knows this is a negative number. The problem is that these numbers are in columns with whole positive numbers in between. I know how to replace/remove the dash, but what I need is some sort of "GoTo" function to find the numbers with the dash, then remove the dash, then put a dash at the beginning of the number. Any suggestions?
Hi Jeff:
In addition to the references that Brian and Aladin have given you, you can also use the following formula. Let us say your entry with the negative sign at the end is in cell A2, then in an adjacent cell ...

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0
and drag it down to corrspond with all the entries in the adjacent column.

HTH

Please post back if it works for you otherwise explain a little further and let us take it from there!

[ This Message was edited by: Yogi Anand on 2002-04-27 13:57 ]

4. =REPLACE("-"&A2,LEN("-"&A2),1,"")+0

Try your formula on the following sample:

{"12-";
"13.0-";
12;
12.3}

5. Hi,

Select your range (or modify the routine to find the range) and run this:

Sub test()
Dim c As Range
For Each c In Selection
If Not c.HasFormula And IsNumeric(c) Then c = CDbl(c)
Next c
End Sub

Bye,
Jay

6. On 2002-04-27 14:21, Aladin Akyurek wrote:

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0

Try your formula on the following sample:

{"12-";
"13.0-";
12;
12.3}
Hi Aladin:
My formula will work only with entries with the - (negative sign) at the end. It will not work with other entries.

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -12 for entry 12-

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -13 for entry 13.0-

Regards!

Yogi Anand

7. On 2002-04-27 14:43, Yogi Anand wrote:
On 2002-04-27 14:21, Aladin Akyurek wrote:

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0

Try your formula on the following sample:

{"12-";
"13.0-";
12;
12.3}
Hi Aladin:
My formula will work only with entries with the - (negative sign) at the end. It will not work with other entries.

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -12 for entry 12-

=REPLACE("-"&A2,LEN("-"&A2),1,"")+0 will give -13 for entry 13.0-

Regards!

Yogi Anand
Yogi,

But the OP said:

The problem is that these numbers are in columns with whole positive numbers in between.

So, what about:

=IF(ISNUMBER(A1),A1,-LEFT(A1,LEN(A1)-1))

Aladin

8. Hi Aladin:
I should have specifically stated that the formula I gave will work only for text entries with the negative sign at the end.

Regards!
Yogi Anand

9. I have modified my formula to correctly handle positive numbers, conventional negative numbers, and the negative numbers entered as text with negative sign at the right end. I appreciate Aladin's emphasizing that my formula posted earlier only handled negative numbers entered as text with negative sign at the end.

=IF(RIGHT(A1,1)="-",REPLACE("-"&A1,LEN("-"&A1),1,""),A1)+0

so the formula resultw will be as follows ...

with 12.3 in cell A1 ... result 12.3
with -12.3 in cell A1 ... result -12.3
with 12.3- in cell a1 ... result -12.3

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:32 ]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•