Results 1 to 10 of 10
  1. #1
    Tony Gater
    Guest

    trailing minus sign in Excel

    Does any one of an Excel setting that will allow me to import text files with values that have trailing minus signs. I have to "strip off" the minus sign in text import wizard and multiply each value X -1, I have set up a custom style to use but this will not save in the style box causing me to re create it every time. This is a continual problem as I import various values for analysis and reports several times a working day.
    Thanks

    Tony Gater

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    I don't know of any excel setting but have had the same problem and found two solutions.

    1. Sometimes the program from which you are importing has a setting, often buried somewhere, that allows you to change trailing minus signs to either leading minus signs or parenthesis. Excel will handle either of these latter two.

    2. An excel add-in nameed The Spreadsheet Assistant at www.add-ins.com will enable you to perform the math (*-1) to a range of numbers after you search and replace the minus sign with "nothing". For entering "nothing" in the replace with box just leave it blank. This add-in has manyother useful commands for working wioth imported data as well as other enhancements. Well worth it if you use excel a lot.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    Hi,

    I would be suggesting that, if you're importing data with the same data format frequently, that you consider some VBA code to do the job.

    I don't know the format of your data- but if you can post a sample or a description (eg, comma delimited? tab delimited? fixed position? embedded quaotes?) I could try to suggest some code.

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    Hi,

    I often have this problem - I tend to fix it after the import stage/event using the following code which i have attached to a custom menu item - I'm posting it partly for you and partly so those better at coding than me can suggest ways to improve on my method. (I've seen many variations on the theme and it would be interesting to see the code of others)

    Note that if you select a range and call the routine, only the selected cells will be processed but if you select a single cell then if the cell is empty all cells above it are processed, otherwise the routine loops down the cells with values in until an empty cell is reached.
    '################################################# #########
    '################################################# #########
    '################################################# #########
    Sub ConvertTailEndNegatives()

    If Selection.Cells.Count > 1 Then

    For Each cell In Selection
    If Right(cell.Value, 1) = "-" Then
    getlength = Len(cell.Value)
    getnumber = Left(cell.Value, getlength - 1)
    newnumber = -1 * getnumber
    cell.Value = newnumber
    End If
    Next

    ElseIf IsEmpty(ActiveCell.Value) Then

    ColVal = ActiveCell.Column
    ActiveCell.Value = "end"
    RowVal = 1
    Do
    If Right(ActiveSheet.Cells(RowVal, ColVal).Value, 1) = "-" Then
    getlength = Len(ActiveSheet.Cells(RowVal, ColVal).Value)
    getnumber = Left(ActiveSheet.Cells(RowVal, ColVal).Value, getlength - 1)
    newnumber = -1 * getnumber
    ActiveSheet.Cells(RowVal, ColVal).Value = newnumber
    End If
    RowVal = RowVal + 1
    Loop Until ActiveSheet.Cells(RowVal, ColVal).Value = "end"
    ActiveSheet.Cells(RowVal, ColVal).Value = ""

    Else

    RowVal = ActiveCell.Row
    ColVal = ActiveCell.Column
    Do
    If Right(ActiveSheet.Cells(RowVal, ColVal).Value, 1) = "-" Then
    getlength = Len(ActiveSheet.Cells(RowVal, ColVal).Value)
    getnumber = Left(ActiveSheet.Cells(RowVal, ColVal).Value, getlength - 1)
    newnumber = -1 * getnumber
    ActiveSheet.Cells(RowVal, ColVal).Value = newnumber
    End If
    RowVal = RowVal + 1
    Loop Until IsEmpty(ActiveSheet.Cells(RowVal, ColVal).Value)

    End If

    End Sub

    HTH

    brooke

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    My suggested code is as follows.

    Note that I have used the "Option Explicit". It adds to the coding effort- but I think it's essential. It's so easy to mistype a variable name, and it can take a long time sometimes to pick up what you've done wrong.

    The code:

    <pre>Option Explicit

    Sub ConvertTailEndNegatives()
    Dim Cell As Range
    Dim RowVal As Integer

    If Selection.Cells.Count > 1 Then
    For Each Cell In Selection
    Cell.Value = ConvertMinus(Cell.Value)
    Next

    ElseIf IsEmpty(ActiveCell.Value) Then
    For RowVal = 1 To ActiveCell.Row
    ActiveSheet.Cells(RowVal, ActiveCell.Column).Value = ConvertMinus(ActiveSheet.Cells(RowVal, ActiveCell.Column).Value)
    Next

    Else
    RowVal = ActiveCell.Row
    Do
    ActiveSheet.Cells(RowVal, ActiveCell.Column).Value = ConvertMinus(ActiveSheet.Cells(RowVal, ActiveCell.Column).Value)
    RowVal = RowVal + 1
    Loop Until IsEmpty(ActiveSheet.Cells(RowVal, colVal).Value)

    End If

    End Sub

    Function ConvertMinus(strValue As Variant) As Variant
    Dim strTemp As String
    If Right(strValue, 1) = "-" Then
    If IsNumeric(Mid$(strValue, 1, Len(strValue) - 1)) Then
    ConvertMinus = -1 * Mid$(strValue, 1, Len(strValue) - 1)
    End If
    Else
    ConvertMinus = strValue
    End If

    End Function
    </pre>



    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    Here's what I use to fix trailing negatives. I also use it to fix negatives in angled brackets which I get if I import reports from the AS400 spool file. It also skips dashed lines that I get from those reports.

    I'm almost embarrassed. My code isn't nearly as sophisticated as what I've seen so far, but it seems to work OK for me.
    <pre>Option Explicit
    Sub FixNeg()
    Dim myCell As Range
    Dim i As Long
    Dim Work As String

    For Each myCell In Selection

    myCell.Value = Trim(myCell.Value)

    If Right(myCell.Value, 1) = "-" And Right(myCell.Value, 2) <> "--" _
    Or Right(myCell.Value, 1) = ">" Then
    Work = "-"
    For i = 1 To Len(myCell.Value) - 1
    If InStr(".0123456789", Mid(myCell.Value, i, 1)) > 0 Then
    Work = Work & Mid(myCell.Value, i, 1)
    End If
    Next i
    myCell.Value = Work
    End If

    Next myCell

    End Sub
    </pre>

    End Sub

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: trailing minus sign in Excel

    Hi,
    Using the CDbl function might be easier - it will immediately convert 1- into -1 and so on. You will either need to check that the cell value is numeric or use a simple On Error Resume Next when looping through the cells though.
    eg If you have a column of figures to do this code would work:
    Sub TrailNegs()
    Dim myCell As Range
    For Each myCell In Selection
    With myCell
    .Value = CDbl(.Value)
    End With
    Next 'myCell
    End Sub

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    G'day to all respondants,

    I appreciate the simplicity of processing the spreadsheet after the import, rather than trying to to process values when they're imported. It makes the programming so much simpler.

    However, If this was something that you did frequently, yo might be better off to do extra work when you're importing, to have a macro do everything.

    Presumably, the person importing has to go through the import wizard stuff, and have to answer at least one question (even if it involves only a click on "finish", IF they're sure of the data format), and then to run a macro to convert imported data. The macro I responded to had three different scenarios according to what was selected; it's quite feasible that the wrong area of the workbook was selected, and none of those scenarios did the right job.

    So would it be better do have a macro to do the conversion at import time? You save the having to run macro import wizard, and any problems associated with having to select a particular bit of the worksheet before you proceed with the next bit.

    Just a thought

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trailing minus sign in Excel

    [img]/w3timages/icons/laugh.gif[/img]Sometimes not being sophisticated, is the better route. I can't comment on any of these scripts, but I know from my WATCOM Basic days, simpler, is often better.

    Besides, you can always expand your script / program to do other things down the road, if required.
    Christopher Baldrey

  10. #10
    Tony Gater
    Guest

    Re: trailing minus sign in Excel

    Thanks guys the code worked fine - saves me oodles of time now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •