Macro to move "-"

[stapuff]

I am not sure of the author of this code - so I can't give proper credit. It is not doing exactly as I need so I need some help.

I have data downloaded where negative numbers are shown as 35,650- which is viewed by Excel as text. The code below moves the "-" from the right to the left thus allowing Excel to understand it as a negative which is what I need.

The problem is it get's trimmed. 35,650- shows as -35 not -35,650. What needs to change to show the whole #?

Thanks,

Kurt


Private Sub texttonegative()
Dim MemberCell As Range, DACELL
For Each MemberCell In ActiveSheet.UsedRange
If Right(MemberCell.Formula, 1) = "-" Then
MemberCell.Formula = Trim(MemberCell.Formula)
For DACELL = 1 To Len(MemberCell.Formula)
If Mid(MemberCell.Formula, DACELL, 1) = Chr(44) Then 'IF HAS COMMA
MemberCell.Formula = Left(MemberCell.Formula, DACELL - 1) _
& Right(MemberCell.Formula, Len(MemberCell.Formula) - 2)
End If
Next
MemberCell.Formula = _
Val("-" & Val(Left(MemberCell.Formula, _
Len(MemberCell.Formula) - 1))) 'Formula for moving sign
End If
Next
End Sub

 

[Mark W.]

Why are you using a macro to accomplish this. Text to Columns... does it for you.

 

[scrupul0us]

I got a nice lil module for ya... Used it many times

Private Sub MoveNegSymbol()
'-------------------------------------->
'MOVES NEGATIVE SYMBOL FROM RIGHT TO LEFT
'-------------------------------------->
'change H2 and K65536 to meet your range requirements
For Each Cell In Range("H2", Range("K65536").End(xlUp).Address)
    If Right(Cell, 1) = "-" Then
        Cell.Value = --("-" & Left(Cell, Len(Cell) - 1))
        Cell.Font.ColorIndex = 3
    End If
Next Cell
End Sub

this also makes the text red

 

[texasalynn]

Here's a macro that I use. But Mark W. is also correct and no macro is really needed. This is a macro I had before I knew that trick

Sub MoveMinus()
On Error Resume Next
Dim Cel As Range
Dim myVar As Range
Set myVar = Selection

For Each Cel In myVar
    If Right((Trim(Cel)), 1) = "-" Then
        Cel.Value = Cel.Value * 1
    End If
Next
 
   With myVar
      .Columns.AutoFit
End With

End Sub

HTH
texasalynn

 

[Mark W.]

...
this also makes the text red

So does the number format...

0;[Red]-0;0

 

[scrupul0us]

Indeed But since you know the number is going to be negative theres no reason to have a selectable case as that provides... Either way works just as well tho

 

[Norie]

Which version of Excel does the Text to Columns.., method work?

I'm using 2000 and can seem to find anything to do with trailing negatives.

 

[Mark W.]

Which version of Excel does the Text to Columns.., method work?

I'm using 2000 and can seem to find anything to do with trailing negatives.

I'm using 2002, but I thought it was supported in 2000. See the [ Advanced... ] button at Step 3 of 3.

 

[Mark W.]

Indeed But since you know the number is going to be negative theres no reason to have a selectable case as that provides... Either way works just as well tho

Until someone edits the value and makes it positive.

 

[Norie]

Checked the advanced button, it only gave me options to do with the Decimal and Thousand seperator.