move negative sign to the front

[agnesz]

I have this scrape that pulls data of off our mainframe into Excel. The problem is that when the number it pulls is negative, it shows the negative symbol (-) AFTER the number, rather than before. So Excel doesn't recognize that value as a negative number.

For example:
-1 shows up as 1-

How can I move the (-) to the front?

Thanks!

[Bob Phillips]

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

[francis]

When data are import into EXcel from external, they are normally text based.
If you using 2003 version, click on the tag smart ( green in color ) at the side of the cell and choose convert to number. It should change the text to number.

Otherwise try Data>Text to Columns>Next>Next>Advanced, make sure
Trailing minus for negative numbers is checkmarked > OK>finished

This will format the cells to numbers.

regards, xlsops

[agnesz]

What if I want the step of substituting to happen during a macro? Meaning right after it scrapes the values into Excel, how do I add thsi part in. The problem with just using SUBSTITUTE for all of the values is that if something came over as positive without the symbol after it, it would still be made a negative.

Can you help?
Thanks!

[Bob Phillips]

[vba]

Dim cell As Range

For Each cell In Columns(1).Cells
If cell.Value <> "" Then
If Right(cell.Value, 1) = "-" Then
cell.Value = -1 * Left(cell.Value, Len(cell.Value) - 1)
End If
End If
Next cell
[/vba]