Converting a Number with Minus Sign on Right to Minus on Left

The information in this article applies to:

• Microsoft Excerl 98 Macintosh Edition
• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

When you import a worksheet into Microsoft Excel and that worksheet contains numbers with minus signs on the right (for example, 12345-), Microsoft Excel may not treat these numbers as negative numbers.

CAUSE

Microsoft Excel treats these numbers as text because the minus sign appears to the right side of the number.

WORKAROUND

There are two ways to solve this problem:

 -Use a worksheet formula.

   -or-

 -Create a macro.

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

1. Enter the following in a worksheet:

         A1: 3
         A2: 2-
         A3: 1-
         A4: 4
         A5: 6-
   

2. In cell B1, enter the above formula.

3. Fill the formula in B1 down to B5.

4. Select cells B1:B5 and click Copy on the Edit menu.

5. Select cell A1 and click Paste Special on the Edit menu.

6. Click Values and click OK.

7. Select B1:B5 and click Delete on the Edit menu.

NOTE: You must select the range of cells containing the values you want to change before you run the macro.

Microsoft Excel Versions 5.0 and Later

   Sub move_minus_left()
      Dim currentcell As Object

      For Each currentcell In Selection
         If Right(currentcell.Value, 1) = "-" Then
            currentcell.Formula = "-" & Left(currentcell.Value, _
               Len(currentcell.Value) - 1)
         End If
      Next currentcell
   End Sub

Microsoft Excel Versions 3.0, 4.0, and 4.0a

   A1: =FOR.CELL("Current")
   A2: =SELECT(Current)
   A3: =IF(RIGHT(Current,1)="-",FORMULA("-"&LEFT(Current,LEN(Current)-1)),)
   A4: =NEXT()
   A5: =RETURN()

Microsoft Excel Versions 2.x

   A1: Switch Minus Sign
   A2: =ACTIVE.CELL()
   A3: =IF(RIGHT(A2,1)="-",FORMULA("-"&LEFT(A2,LEN(A2)-1)),)
   A4: =RETURN()