Fixing trailing minuses...

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:

    123.4-

XL doesn't automatically translate these numbers, instead importing them as text. Here are several workarounds:

Using a formula

You can use a "helper column" with a formula like this in, say, row 1:

    =IF(ISNUMBER(A1),A1,-LEFT(A1,LEN(A1)-1))

or

    =IF(ISNUMBER(A1),A1,-SUBSTITUTE(A1,"-",""))

Copy down as far as necessary. You can then Copy the column, select the original column, choose Edit/Paste Special, select the Values radio button and click OK. Now you can delete the helper column.

Using the Text Wizard

In XL02/03 (Windows) you can select one column of your data, choose Data/Text To Columns..., click OK, OK, then Advanced. Check the treat trailing minus as negative checkbox, then Finish.

Using a macro

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 Thursday, 11 August 2005