Excel - problem importing negative numbers from text file 4

[PPettit]

When my accounting software generates a report, it writes all negative numbers with the dash (or "minus sign") after the number (ex: 123.45-).

Every now and then, I need to import these reports into Excel so that I can do some additional manipulation of the data. My problem is that Excel treats those negative numbers as text since they don't follow the usual format for negative numbers (ie. "-123.45" or "(123.45)"). I have to fix each negative number by hand if I want to have the totals calculated correctly.

Is there an easy way (in Excel) to convert these numbers from "###.##-" to "-###.##"?

 

[SkipVought]

Hi,

In a new column, assuming that your values are in column A...
[tt]
=if(right(A1,1)="-",left(A1,Len(A1)-1)*-1,A1)
[/tt]
Copy down thru data rows

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[PPettit]

Thanks, Skip. This should work just fine.

 

[Gavona]

Sounds like this will be an ongoing issue. Try this macro which I wrote to resolve the same issue.

Sub MyNegNumbers()
'Converts any cell in current selection _
  with a number plus trailing "-"   into a _
  number that excel recognises _
  e.g.from 9999- to -9999
'does not affect cells where removing the _
 trailing zero does not leave a single numeric value _
 is therefore 'safe' to use.
Dim c As Range
Dim myvar As String

For Each c In Selection
If Len(c.Value) = 0 Then
Else

myvar = Val(Left(c.Value, Len(c.Value) - 1))
    If Right(c.Value, 1) = "-" Then
        If Len(myvar) = Len(c.Value) - 1 Then
        c.Value = myvar * -1
        End If
    End If
End If
Next c
End Sub

Gavin

 

[PPettit]

Thanks, Gavin.

This seems to work well, also. It's always a good idea to have more than one way to accomplish the same task.

 

[davidmo]

Gavin:
I used your Macro since this problem is a regular issue with me and it works great. I did notice that if the number ended in a zero (###.#0-)that it would not convert the number. Do you have any ideas?

Thanks.

David

 

[SkipVought]

Hi,

That is NOT a conversion issue -- its a DISPLAY issue.

Trailing zeros on DECIMAL, or leading zeros on INTEGER can be addressed using a DISPLAY FORMAT.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Gavona]

Well spotted David. I don't think that I tested it at all with decimal numbers (didn't apply to my data). My intention for my original application was that one could select the entire used range and this would solve the issue without inadvertently changing cells ending in "-" that were not numeric.

I think that this fixes it, though I haven't tested extensively. Do let me know if you find any other bugs.

Sub MyNegNumbers()
'Converts any cell in current selection _
  with a number plus trailing "-"   into a _
  number that excel recognises _
  e.g.from 9999- to -9999
'does not affect cells where removing the _
 trailing zero does not leave a single numeric value _
 is therefore 'safe' to use.
Dim c As Range
Dim myvar As String

For Each c In Selection
If Len(c.Value) = 0 Then
Else

myvar = Left(c.Value, Len(c.Value) - 1)
    If Right(c.Value, 1) = "-" Then
        If Len(myvar) = Len(c.Value) - 1 Then
        On Error Resume Next
        c.Value = myvar * -1
        On Error GoTo 0
        End If
    End If
End If
Next c
End Sub

Best wishes


Gavin

 

[Gavona]

For Info: In Excel 2003: Data,Text to columns, advanced allows one to specify trailing minus signs.


Gavin