Changing Position of the Negative Sign

[Jeff Steele]

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?

[Brian from Maui]

Jeff

Try this thread

http://www.mrexcel.com/board/viewtop...c=1455&forum=2

[Aladin Akyurek]

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

[Yogi Anand]

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 ]

[Aladin Akyurek]

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



Try your formula on the following sample:

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

[Jay Petrulis]

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

[Yogi Anand]

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

[Aladin Akyurek]

On 2002-04-27 14:43, Yogi Anand wrote:

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

[Yogi Anand]

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

[Yogi Anand]

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 ]