• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert negative number into bracket

S

Sandy_Patel

New Member
Hi

In Excel school, video of lesson of formatting-paste-special of Week 3, it was shown that negative number can be converted in bracket by clicking on dollar symbol sign which is in number format option of Home menu of Excel . This point is discussed between 14:04 to 14:25 mins in video. When I click on dollar sign , result is that dollar sign ($) is appeared with number in cell but negative number can not be converted in bracket as discussed in video. Please advice me on this.

Thanks
Sandip
 
I suggest you to pick up a new sheet and try this method because it works. There are two ways of putting brackets on a negative number, if you want a dollar sign click dollar sign in the ribbon, if just brackets then click comma. Both are working on my pc. (XL 2007)
 
If you mean that you don't want it to show a dollar sign and instead just keep the negative number in brackets the you'll need to use an IF condition for less than 0 value and add brackets basis that - note that the number will be converted to text by doing so - formula below (assumes negative number is in A1):

Code: 
=IF(A1<0,"("&A1&")",A1)
 
Faseeh said:
I suggest you to pick up a new sheet and try this method because it works. There are two ways of putting brackets on a negative number, if you want a dollar sign click dollar sign in the ribbon, if just brackets then click comma. Both are working on my pc. (XL 2007)
Click to expand...


Hi Faseeh

Thanks for quick and nice reply.
Still not reached solution to my question.
I followed your guided that I opened new sheet and clicked on comma and dollar sign in the ribbon. I use excel 2013 which I have just upgraded.
I have tried in excel 2007 that I clicked on dollar sign in ribbon but it did not convert number in bracket in excel 2007. I know how to convert negative value into bracket by formula and custom format but am interested to know to convert negative number very instantly.

I do not know why that work in ur sheet and not in mine.
Hope, excel 2007 and 2013 will have same effect of this dollar sign and comma in sheet.
If possible , could you attach sheet so I can try on my laptop.

Thanks again
 
chirayu said:
If you mean that you don't want it to show a dollar sign and instead just keep the negative number in brackets the you'll need to use an IF condition for less than 0 value and add brackets basis that - note that the number will be converted to text by doing so - formula below (assumes negative number is in A1):

Code: 
=IF(A1<0,"("&A1&")",A1)
Click to expand...


Hi Chirayu

thanks for reply. I appreciate your time making reply to me.

I know how to convert negative number by formula and custom format. I am interested to know convert negative value in bracket by one or two click of mouse meaning clicking on dollar sign or comma in ribbon.

Good to know new formula from you. Pls guide me if u know more .

thanks
 
Hi @Sandip_Patel,

Mentioning $ option was a mistake on my side, but still the comma option is working for me. Can you the attached file?
 

Attachments

  • Sandeep_nagetive num.xlsx
    9.4 KB · Views: 6
Comma option working in Excel 2010.

This what it shows when you apply comma format in custom format box.

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

Try pasting this in custom format dialogue box.

Regards,
 
Hi Faseem

I have tested your sheet and it work for me. I can convert negative value by clicking on comma sign in ur sheet. But i can not do in my new sheet which is of 2013 version. Find attached sheet which I made on 2013 version.

Not sure if this option not available in 2013 excel to convert negative value in bracket by clicking on comma sign.
 

Attachments

  • Testing - negative value.xlsx
    7.7 KB · Views: 1
Hi Somendra Misra
I tried code and it work for me.

But I really want to convert negative value by clicking on comma sign and it is not working in 2013 excel sheet.
 
Hi Sandeep,

Use this custom number format, its working on your sheet: #,##0 ;[Red](#,##0);- ;

Please see the file:
 

Attachments

  • Testing - negative value.xlsx
    8.3 KB · Views: 3
Faseeh said:
Hi Sandeep,

Use this custom number format, its working on your sheet: #,##0 ;[Red](#,##0);- ;

Please see the file:
Click to expand...
Hi Faseeh

I know , i can do it by custom format but i want to know converting negative value by one or two click of mouse which is faster than custom format code.

I need to work out in 2013 excel version .
 
Thanks every one to reply me .
I have got solution to my question. It was lovely reply got to my first ever question on this formu

Cheers every one
 
Somendra Misra said:
Just see below link:

http://support.microsoft.com/kb/832285

Regards,
Click to expand...

Hi Misra

You got magic for me. It really worked for me. Earlier u advised me to make changes to Regional and language but i have limited knowledge of control panel.

I opened above link and made changes. Now I am able to convert negative value into bracket by clicking on comma. Only one small thing is still unresolved that when I pressed on dollar sign then negative number gets converted into bracket but dollar symbol ($) appears outside of bracket in cell as $(-100). Normally we want to show negative value with dollar sign as ($100).

Find screen shot of customise format in which I made changes.
I also attached my excel sheet so u can have a look .

I use windows 7.

Thanks
 

Attachments

  • Customise Format - screen shot.PNG
    Customise Format - screen shot.PNG
    80.8 KB · Views: 2
  • Negative number - sheet.xlsx
    9 KB · Views: 6
You must log in or register to reply here.
Back
Top