Move minus sign

[erodjob]

I have a download in which some of the numbers in the download have a minus sign to the right of the value.

For example

I get 23000- 1400- how can I move the minus sign to the left?

I want the value to be -23000 or -1400.

I would like this in a macro of some kind.

The data is in Range("B1:B2000").

Thanks

[Aladin Akyurek]

Try ASAP Utilities which is free from:

http://asap-utilities.com

[Von Pookie]

Just download ASAP Utilities.

It is a feature under the "Number" menu.

Hope it helps,

[Philem]

From www.xl-logic.com comes this handy macro:

Sub DashMasher()
' To convert 15- to -15. 
    On Error Resume Next
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Set TextCells = Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    n = TextCells.Count
    For Each Cell In TextCells
        x = Cell.Value
        If Right(x, 1) = "-" Then
            x = -Left(x, Len(x) - 1)
            Cell.Value = x
        End If
        c = c + 1
        Application.StatusBar = "Percent Complete:  " & Int(c / n * 100) & "%"
    Next Cell
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

HTH

P

[Andrew Poulsom]

Another way:

Sub Test()
    Dim Rng As Range
    Columns("C:C").Insert
    Set Rng = Range("C1:C" & Range("B1").End(xlDown).Row)
    Rng.FormulaR1C1 = _
        "=(SUBSTITUTE(RC[-1],""-"","""")+0)*IF(RIGHT(RC[-1],1)=""-"",-1,1)"
    Rng.NumberFormat = "General"
    Rng.Copy
    Rng.Offset(, -1).PasteSpecial xlPasteValues
    Rng.Offset(, -1).PasteSpecial xlPasteFormats
    Columns("C:C").Delete
End Sub

[erodjob]

Originally Posted by Andrew Poulsom

Another way:

Sub Test()
    Dim Rng As Range
    Columns("C:C").Insert
    Set Rng = Range("C1:C" & Range("B1").End(xlDown).Row)
    Rng.FormulaR1C1 = _
        "=(SUBSTITUTE(RC[-1],""-"","""")+0)*IF(RIGHT(RC[-1],1)=""-"",-1,1)"
    Rng.NumberFormat = "General"
    Rng.Copy
    Rng.Offset(, -1).PasteSpecial xlPasteValues
    Rng.Offset(, -1).PasteSpecial xlPasteFormats
    Columns("C:C").Delete
End Sub

Thanks

[Stormseed]

Originally Posted by Aladin Akyurek

Try ASAP Utilities which is free from:

http://asap-utilities.com

On the website, it says (when used in a commercial org) that we need to buy a License key for using ASAP utilities, while you say it is free - Is it free for home use only ?