# Thread: Negative sign alignment (Win2K Office 2K sp3)

1. ## Negative sign alignment (Win2K Office 2K sp3)

How do I tell excel that a copied/pasted value with a minus sign on the right is a negative number? I've tried the various format cell options and it appears that excel doesn't support this format. I even changed my regional settings in the control panel to define negative number as having the minus sign on the right and this didn't help either. I have hundreds of numbers I need to copy & paste into a sheet so that I can sum them. But if it won't recognize those that are negative then I guess excel is of no use in this situation.

2. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Here's a formula that works for me... It checks the last digit in the value and if it's a "-" then it drops the - and multiplies the value by negative 1... (Note: D16 is the cell I was playing with on my sheet so this will have to be changed... )
=IF(RIGHT(D16,1)="-",LEFT(D16,LEN(D16)-1)*-1)

Does this help?

3. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Thanks Trudi - yes that will help.

4. ## Re: Negative sign alignment (Win2K Office 2K sp3)

I tried this but how do I avoid having to enter the formula for every cell in the column?

I want to apply that formula to every cell in column A (potentiall every row).

And I don't know how many digits may appear in each number. Typical numbers are 51,234.56 down to .01 and then the same potential for them having a negative sign on the right.

Sigh... I can't believe Excel can't support defining a number as being negative if it has a minus sign on the right.

5. ## Re: Negative sign alignment (Win2K Office 2K sp3)

If your data are in A1:A100 or something like that, put the formula =IF(RIGHT(A1,1)="-",LEFT(A1,LEN(A1)-1)*-1,A1) in cell B1. Keep cell B1 selected and double click the fill grip (the black square in the lower right corner)

6. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Hans - I don't see a black sqare in the lower right corner. "fill grip' doesn't return anything useful in the help either. Can you be more specific?

Regards,

7. ## Re: Negative sign alignment (Win2K Office 2K sp3)

The thingy is called the fill handle, see picture below.

Once you're close enough to the fill handle, the mouse pointer changes to a cross (as shown on the picture).

You can:

- double click to fill alongside a column matching the length of that column provided that the cells below your cell are empty
- drag with left button to fill
- drag with right button and get a menu with fill options.

8. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Muchas Gracias.

I was able to do the same using the Edit/Fill option off the drop down menu. I do see the 'thingy' now. I was looking in the lower right of the sheet itself. This method appears to work. Thank you all for your wonderful, timely help. And my boss thanks you too.

Have a great day !!

9. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Just for completeness, do you know what Keyboard presses to use to "drag" the fill handle if you're not using a mouse, I've seen it done but didn't ask the person at the time.

StuartR

10. ## Re: Negative sign alignment (Win2K Office 2K sp3)

The Shift key anchors the first cell of your range. Ctrl takes you to end of a contiguous range.

If I have a range which is filled in with a formula and then I revise the formula, I will edit the first formula and then hit ctrl-c (to copy), then use shift-ctrl-down or shift-ctrl-right or shift-ctrl-end to highlight my range, and then hit ctrl-v to paste.

11. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Try this one. For example you have a formula in cell B1 and you want to copy it to B2:B10. Place your cursor in cell B10 making it the activecell. Hold down the Shift Key, press the End Key and press the up arrow key. The following range should be highlighted: B10:B1. Then press Ctrl-D. All cells in the highlighted range will now contain the cell formula in B1.

John

12. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Nearly, but this doesn't get the Autofill functionality I have seen.

Suppose I have "January" in cell C10, how do I get February - December in Cells C11 to C21. I know I can do it by dragging the fill handle, but I have seen the same effect achieved by someone who didn't remove their hands from the keyboard.

StuartR

13. ## Re: Negative sign alignment (Win2K Office 2K sp3)

Since all menu commands etc. can be reached from the keyboard, the following would do it, although it seems more tedious than using the mouse (keystrokes are for Excel 2002)
- Type January in cell C10
- Use Shift+down arrow to extend selection to cell C21
- Press Alt+e i s Alt+f Enter (Edit menu, Fill, Series, AutoFill, OK)

14. ## Re: Negative sign alignment (Win2K Office 2K sp3)

It certainly would do it, but whatever keystroke this guy used, he was able to use the down-arrow to continue the autofill after starting it (he had something pressed on the keyboard too). Next time I see him I'll ask and post here...

StuartR

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•