How to prefix Plus sign "+" before the number if the value is negative in Google Sheets or MS Excel?

Question

I am trying to add/prefix a plus sign "+" if the value is negative. e.g. +10

I have sheet where some bill payments are made and sometimes they overpay. So, the Balance column should reflect like +(over paid value).

I tried with =CONCATENATE but this does not help. It comes as +-10

=IF(C2<0,CONCATENATE("+",C2),"")

Answer 1

You can try going to Format > Number > More formats > Custom number format and inserting the following expression: +0;-0;0

This will cause negative values to be show with a + sign, and positive ones to be shown with a - sign. The value that it holds, however, will still be the original one (as a number).

See docs (code formatting added):

When creating a custom format, note that the formatting can consist of up to 4 parts separated by semicolons: positive;negative;zero;non-numeric.

Answer 2

=if(C2<0, "+" & ABS(C2),  C2)

Or

=If(C2<0,"+","")&ABS(C2)

Answer 3

all you need is this:

=IF(A2-B2<0, A2-B2*-1, )

then Arrayformula would be:

=ARRAYFORMULA(IF(A2:A-B2:B<0, A2:A-B2:B*-1, ))

Answer 4

Try this formula:

=SUBSTITUTE(C2,"-","+")

Answer 5

If you're sure that all cells will have numbers, then you may format cells to have "Number" rather than "General". Then, rewrite your condition as follows: =IF(C2<0,CONCATENATE("+", -1 * C2),"").

I hope it helps.

Answer 6

Try this formula:

=IF(C2<0,"+","")&C2