Microsoft KB Archive/42964

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

Article ID: 42964

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q42964



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:
 * 1) Use a worksheet formula. -or-


 * 1) Create a macro.

Use the following formula in a worksheet:   =IF(RIGHT(A1,1)=&quot;-&quot;,&quot;-&quot;&LEFT(A1,LEN(A1)-1),A1)*1 For example, follow these steps:

  Enter the following in a worksheet:      A1: 3 A2: 2- A3: 1- A4: 4 A5: 6-  In cell B1, enter the above formula. Fill the formula in B1 down to B5. Select cells B1:B5 and click Copy on the Edit menu. Select cell A1 and click Paste Special on the Edit menu.</li> Click Values and click OK.</li> Select B1:B5 and click Delete on the Edit menu.</li></ol>

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) = &quot;-&quot; Then currentcell.Formula = &quot;-&quot; & Left(currentcell.Value, _              Len(currentcell.Value) - 1) End If     Next currentcell

End Sub

Microsoft Excel Versions 3.0, 4.0, and 4.0a
<pre class="fixed_text">  A1: =FOR.CELL(&quot;Current&quot;) A2: =SELECT(Current) A3: =IF(RIGHT(Current,1)=&quot;-&quot;,FORMULA(&quot;-&quot;&LEFT(Current,LEN(Current)-1)),) A4: =NEXT A5: =RETURN

Microsoft Excel Versions 2.x
<pre class="fixed_text">  A1: Switch Minus Sign A2: =ACTIVE.CELL A3: =IF(RIGHT(A2,1)=&quot;-&quot;,FORMULA(&quot;-&quot;&LEFT(A2,LEN(A2)-1)),) A4: =RETURN

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00 4.0a 4.00a 5.0 import mainframe miniframe main mini frame export

Keywords: kbprb KB42964

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.