Change a negative sign to positive

Mjkqb

New Member
Joined
Aug 4, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
What is the best way to change a negative number with a minus sign to display with a plus sign.
I don't want to affect any positive numbers, only negative.
-1 should display as +1.
1 should display as 1.

Below is the formula I'm using.
=MIN(ROUND(MIN(B10,18)*112/113+63.3-70,0),18)

Can this formula be modified to include the minus/plus change?

Please let me know if you need more info.
Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
If i understand you can use:

=A1*SIGN(A1)

This will produce positives as they were and negatives will become positive. That what you meant?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,266
Office Version
  1. 365
Platform
  1. MacOS
ABS(A1)
Returns the absolute value of a number. The absolute value of a number is the number without its sign.

so +ve numbers stay positive and -ve numbers become positive
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,120
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What is the best way to change a negative number with a minus sign to display with a plus sign.
Do you only want to change the displayed value, or alter the actual value?
 

Mjkqb

New Member
Joined
Aug 4, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you only want to change the displayed value, or alter the actual value?
Only the displayed value.
If it is a negative number it needs to be displayed as a positive number with a plus sign.
The other positive numbers should be absolute with no sign.
Including a screen shot.
You can see there are only a couple of cells with a -1 value, but they must be displayed as +1
 

Attachments

  • Help_minus_positivePNG.PNG
    Help_minus_positivePNG.PNG
    99.1 KB · Views: 4

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,120
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
All you should need is a custom number format like:
0;”+”0
 
Solution

Mjkqb

New Member
Joined
Aug 4, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
All you should need is a custom number format like:
0;”+”0
Thanks so much. That worked perfectly. Pretty new at this, just curious about the quotation marks. I had to remove them as they were being displayed.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,578
Messages
5,770,949
Members
425,653
Latest member
UNSING

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
Top