Convert Trailing Negative Sign

With help from another site, here are some options:

1. If you have Excel XP, there is an option under Text to Columns that will allow you to identify Trailing Minus for Negative Numbers.

2. This formula will convert your numbers:
=IF(RIGHT(A2)="-",-SUBSTITUTE(A2,"-",""),A2)

3. This macro will do the same without creating a new column of data:

Sub Negsignleft()
' Gord Dibben
    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.Range("A:A"). _
            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

Credit this one to Gord Dibben (Microsoft MVP).

Hope these options help you out.

Re: Negative values and leading zeros

Hi Seti,

Thanks. Your formula is working fine.

Can't we make the code which you have given to refer to the selection instead of Column 'A', as it is in your current code.

Sorry, lost touch with excel..it's been a long time...

Thanks.

Re: Negative values and leading zeros

Wow, a post response from 2 years ago. I don't even remember this post. However, this code should use your selection, rather than column A. Also, I am not the author of the original code.

Sub Negsignleft()
     ' Gord Dibben
    Dim cell As Range
    Dim rng As Range
     ''move minus sign from right to left on entire worksheet
    On Error Resume Next
    Set rng = Selection.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

Re: Negative values and leading zeros

Quote from tomlon

My manufacturing system extracts negative numbers in an unconventional way (at least for me). For instance -261 it lists it as 00000261- (for positive, it just list 261). It will import into excel (from the text file) into this format. What formula can I write to convert it to -261 when in Excel and extract the leading zeros and move the +/- sign to the front.

Thanks

Custom format the cells like: +General;-General;General as adding a + for positives will make them text. To convert trailing negatives, try

=IF(RIGHT(A1)="-",SUBSTITUTE(A1,"-","")*-1,A1)

Then copy and Edit>Paste Special - Values