negative numbers and brackets

[uk747]

When i enter something like (6789) it always appears as -6789 is there somewhere in options to turn it off

PS sometimes letters will be involved in the brackets i.e. (A456D)

Im using Excel 2002

 

[FormR]

Try to enter as

'(1234)

Or format the cells as text before data entry.

 

[uk747]

Try to enter as

'(1234)

Or format the cells as text before data entry.

Try to enter as

'(1234)

Thats what I'm having to do but when there are lots of occurences it takes along time

Or format the cells as text before data entry.
Just tried that. formatted the column as text then repaste the data and still shows with negative and when i paste it in the fromat reverts back to general

 

[martindwilson]

right click
paste special /text
over a text formatted cell

 

[uk747]

right click
paste special /text
over a text formatted cell

Thanks Martin and as said only works when already formatted as text otherwise reverts back to General

 

[pebloom61]

Have you tried custom number formatting such as #,##0_);(#,##0)

 

[uk747]

Have you tried custom number formatting such as #,##0_);(#,##0)

thanks that also works

 

[Rick Rothstein]

thanks that also works

Just keep in mind, formatting a cell only affects the displayed text in the cell, the underlying value in the cell will remain unchanged... for your case, that means the cells will still contain negative numbers. I am not sure if that will matter to you or not in you ultimate usage of that data, so I figured I would mention it to you "just in case".

 

[uk747]

Just keep in mind, formatting a cell only affects the displayed text in the cell, the underlying value in the cell will remain unchanged... for your case, that means the cells will still contain negative numbers. I am not sure if that will matter to you or not in you ultimate usage of that data, so I figured I would mention it to you "just in case".

Thanks Rick

I did notice but only because in a different part of the sheet I used a lookup table to collate some of the information and as you mentioned it pulled through the negative values. So i just fromatted that data as well with the custom format.

 

[pebloom61]

Great catch Rick. There is so much to learn and keep in mind on this stuff!