trailing minus sign in Excel

Topic

New Reply

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

Reply | Quote

Replies

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 http://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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

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

Geoff

Reply | Quote

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.

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

End Sub

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote