How do I move the sign in Excel

[Tison]

I have an Excel spreadsheet with a column of numerics of format nnnn.nn- eg 1234.56-
How do I move the sign to front of value ie -1234.56
(The format cells function does not work).

 

[pjm]

Hi Tison

If cell formatting doesn't work then it looks like your numerics are in fact stored as text - were they imported from a text file?

If so, you'll have to turn them into proper numbers like so-

=IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))

where A1 is the cell with your number in.

HTH

pjm

 

[Tison]

Thanks PJM, but how do I get it to do this for an entire column ?

 

[pjm]

Hi again.

Enter the formula in a blank column to the right of your data and copy it down to the same length. (Edit Copy/Left, End, Down, Right, Shift-Ctrl-Up Enter.) You can then "fix" the values by using Edit Copy, Edit Paste Special, Values, and then replace your existing numbers.

Regds,
pjm

 

[Dreamboat]

Format-Cells-Custom

#,##0_);#,##0-

If that doesn't work, then Excel is not seeing your data as numbers. In order to force it, put a 1 in a cell somewhere. Then, copy that cell. Then, select all your numbers (that aren't working properly). Then, hit Edit-Paste special-Multiply. Then, reapply the number formatting above.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[SimonDavis]

Is it also possible the the international settings in control panel are set to this strange format?

 

[Dreamboat]

Doh! I thought you were trying to get the hyphen to the right, not the left!

x-)

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com