This site will look better if you upgrade to a browser that supports web standards.
Sometimes text files brought in from other systems use post-fixed negatives, where instead of the more familiar
-123.3 or (123.4)
the minus sign trails the number:
XL doesn't automatically translate these numbers, instead importing them as text. Here are several workarounds:
You can use a "helper column" with a formula like this in, say, row 1:
Copy down as far as necessary. You can thenthe column, select the original column, choose , select the radio button and click . Now you can delete the helper column.
In XL02/03 (Windows) you can select one column of your data, choose, click , , then . Check the checkbox, then .
For other XL versions you can use the fact that VBA can automatically convert the numbers to correct them in place:
Public Sub ConvertTrailingMinuses() Dim cell As Range On Error Resume Next For Each cell In ActiveSheet.Cells.SpecialCells( _ xlCellTypeConstants, xlTextValues) With cell If IsNumeric(.Value) Then _ .Value = CDbl(.Value) End With Next cell On Error GoTo 0 End Sub
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.