Changing Position of the Negative Sign

Jeff Steele

New Member
Joined
Apr 26, 2002
Messages
7
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? :confused:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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? :confused:

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top