negatve numbers within a concatenate formula

[jjquincy]

how do you show a negatve number in a concatenate formula as (38),not as -38

I want brackets to appear instead of the minus sign

thanks for any help

 

[Tazguy37]

After you concatenate the numbers, they aren't numbers anymore -- they are text. I'd suggest using an IF() statement to format the numbers as you want them. e.g. =IF(E7<0,D7&"("&E7&")",D7&E7)

Hope this helps,

 

[Joke]

You have to use TEXT(cell, "format") only that I don't know how to put the format including negative numbers. But you could add a conditional though:


=concatenate(if(a1>0,a1,text(1,"(0)"))

Actually this doesnot get rid of the minus sign but only adds the parenthesis for you.

Joke

 

[Aladin Akyurek]

Something like:

=A1&" "&IF(B1<0,"("&ABS(B1)&")",B1)

where B1 is either positive or negative.

 

[fairwinds]

Hi,

Use text function e.g.

=TEXT(F7,"0.00;(0.00)")

 

[fairwinds]

To answer your locked post:


=CONCATENATE(TEXT(A1,"0;(0)"),"/",TEXT(B2,"0;(0)"),"/",TEXT(C3,"0;(0)"),"/",TEXT(D4,"0;(0)"))

 

[Domenic]

how could I show this 45/-38/664/193 as 45/(38)/664/193

my current formula is =concatenate(a1,"/",b2,"/",c3,"/",d4)

I would like to change that b2 cell to appear as (38) no minus sign


Thanks for all the previous advice

=CONCATENATE(A1,"/","("&ABS(B1)&")","/",C1,"/",D1)

Hope this helps!