Converting a Number with Minus Sign on Right to Minus on Left (42964)

WORKAROUND

There are two ways to solve this problem:

1. Use a worksheet formula. -or-
   
   
2. Create a macro.

Use the following formula in a worksheet:

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

For example, follow these steps:

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.

Use the appropriate macro below for your version of Microsoft Excel.

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()