Macro to move "-"

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Why are you using a macro to accomplish this. Text to Columns... does it for you.
 

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641
I got a nice lil module for ya... Used it many times

Code:
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Code:
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

scrupul0us said:
...
this also makes the text red :)

So does the number format...

0;[Red]-0;0
 

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Norie said:
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
scrupul0us said:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Checked the advanced button, it only gave me options to do with the Decimal and Thousand seperator.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top