Jeff
Try this thread
http://www.mrexcel.com/board/viewtop...c=1455&forum=2
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 ...
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?
Jeff
Try this thread
http://www.mrexcel.com/board/viewtop...c=1455&forum=2
Also you can use ASAP-utilities, free downloadable at:On 2002-04-27 09:51, Brian from Maui wrote:
Jeff
Try this thread
http://www.mrexcel.com/board/viewtop...c=1455&forum=2
http://asap-utilities.com
which has an option under Numbers for moving - from back to front.
Aladin
Hi Jeff: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?
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 ]
=REPLACE("-"&A2,LEN("-"&A2),1,"")+0
Try your formula on the following sample:
{"12-";
"13.0-";
12;
12.3}
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
Hi Aladin: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}
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,On 2002-04-27 14:43, Yogi Anand wrote:
Hi Aladin: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}
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
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
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
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 ]
Like this thread? Share it with others