changing the placement of the negative sign

[njbeancounter]

I imported a text file into excel, only to find that the "-" negative sign is placed after the number. Since the file is over 4000 lines, I cannot replace them one by one. I am only interested in the numbers in one column, which is column "m". Also, not every line contains a number, and some of the cells contain positive numbers.

I am trying to learn vba, but my boss is much less patient than I am.

 

[onlyadrafter]

Hello,

Does this help at all?

Sub neg_sign()
For MY_ROWS = 1 To Range("M65536").End(xlUp).Row
If Right(Range("M" & MY_ROWS).Value, 1) = "-" Then
    Range("M" & MY_ROWS).Value = "-" & Left(Range("M" & MY_ROWS), Len(Range("M" & MY_ROWS)) - 1)
End If
Next MY_ROWS
End Sub

 

[fairwinds]

Hi,

You could put a formula in an adjascent column...

=IF(A1="","",--(IF(RIGHT(A1)="-","-","")&SUBSTITUTE(A1,"-","")))

 

[Aladin Akyurek]

Excel 2003's Data|Text to Columns has an option for trailing minus sign, which you can run to resolve the issue.

Otherwise:

In X2 enter & copy down:

=IF(N(M2),M2,-SUBSTITUTE(M2,"-",""))

 

[njbeancounter]

Thanks !!! Just when I think I know what I'm doing, I realize that I know just enough to be dangerous...