Help with format issue Excel 2000

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hello

When I open a text file from my Unix application in Excel, negative
numbers come over with the negative sign on the right, i.e. 26.35-. Is
there an easy way to switch the negative sign to -26.35 so Excel will
know it's a negative number?

TIA

Rich
 
Ok, I found what I was looking for at Microsoft.com

Note Some accounting programs display negative values with the negative
sign (–) to the right of the value. To convert the text strings to
values, you must return all the characters of the text string except the
rightmost character (the negation sign), and then multiply the result by
–1. For example, if the value in cell A2 is 156– the following formula
converts the text to the value –156:

=LEFT(A2,LEN(A2)-1)*-1

But now, I have many cells that need to be corrected, I'm trying to
write a little script in the editor, something like this. VB doesn't
like the "contains":

Sub Negative()
For Each x In Range("A1:B4")
If x.Value contains "-" Then LEFT(x.value,LEN(x.value)-1)*-1
On Error Resume Next
Next x
End Sub
 
Depends upon which version of Excel you're running.

I believe 2002 introduced the Data>Text to Columns>Next>Next> "Advanced"

Prior to that you could use VBA code.

Sub Negsignleft()
Dim Cell As Range
Dim Rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set Rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each Cell In Rng
If IsNumeric(Cell.Value) Then
Cell.Value = CDbl(Cell.Value)
End If
Next Cell
End Sub

Gord Dibben Excel MVP
 
Rich

Thanks for the thanks.

Anybody out there know if Excel 2000 had the "trailing minus sign" fix in
Data>Text to Columns?

Excel 97 did not, for sure.

Gord
 
Back
Top