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),"")

Solution

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).

Comments

[kalyan]

thanks. it actually works but only visually. The value ceases to be a number and hence it does not count when added with another number.

[lapo]

I had to use +0;-0;0 or it would put a - in front of positive numbers.