How to put a numeral value in parenthesis below '+' sign in same cell

[sonia]

I've uploaded a section of my lengthy excel sheet at . I have to put a numeral value in parenthesis or without parenthesis under the + signs in some of the cells but when i write it the + sign disappears but i want both in a single cell preferably with numeral value below the sign.

Only options which comes in mind is insertion of row beneath the cell where i wish to put both sign & numeral value but this seems highly impracticable solution. Kindly suggest some other alternative.

 

[Hui]

Sonia

Firstly, Welcome to the Chandoo,org Forums


You can do this in a few ways


Manually

Use a formula like

="+"&CHAR(10)&N44

Make sure word wrap is enabled in the cells

Adjust Row height to twice normal


Custom Number Format

use a Custom Number Format like

"+" Ctrl J 0.0

Make sure word wrap is enabled in the cells

Adjust Row height to twice normal


Using the Custom Number Format is probably preferred as the cell still contains the number and the +/- won't impact any other cells that link to the cell

 

[sonia]

Thanks Hui but both suggested options are leading to appearance of "" marks which is undesirable. Plz suggest other one

 

[anupamtiwari05]

Hi Sonia,


Please try the below one and let us know if its working fine for you.


=IF(N44="","",CHAR(43)&CHAR(10)&N44)


Or


=IF(N44="","",CHAR(43)&N44)


Thanks & Regards,

Anupam Tiwari

 

[Hui]

Custom no format

+ Ctrl J 0.0

 

[P0lar]

Hi Sonia,


I had a go and got the following to work -

In a cell, e.g. A1 type

'+ [ALT + Enter] (N)

Then use

= SUBSTITUTE(A1,IF(N44>0,"(N)","N"),N44)

Then paste the format from A1 to the cell you want to display the value - I have no idea why this step works as both cells are formatted as general, but it did.