Negative numebrs in Excel

[Angela]

Does anyone know how I can get Excel to correct/reformat
numbers which have the minus sign after the number?

I have recently received a new laptop which runs XP (my
old laptop ran win2000) on both machines I ran/run Excel
2000.

In the course of my work I have to cut and paste a large
amount of data from another application. The other
application shows the minus sign AFTER the number. When I
copy it into Excel, it recognises it as text and not a
number. I have tried concatenating to see if I can
automate it someway, but it just gives me #VALUE! I would
rather not have to mess around with putting in formulas
anyway, because the nature of the spreadsheet does not
easily lend itself to adding formulas.

It must be possible because I never had a problem with it
before. Can anybody else help?

I really hope so, I'm being slowed down a lot now -
despite my new 'whizzy' laptop!

 

[Gord Dibben]

Angela

You did say you are running Excel 2000, right?

Copy/paste this code to a general module in your workbook then run it.

Sub ChangeSign()
Dim cell As Range
''to move a - sign from right to left in Column A
On Error Resume Next
For Each cell In Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp)). _
SpecialCells(xlConstants, xlTextValues)
If Right(Trim(cell.Value), 1) = "-" Then
cell.Value = CDbl(cell.Value)
End If
Next
On Error GoTo 0
End Sub

If not familiar with VBA macros, see David McRitchie's getting started page.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

OR post back for details.

Gord Dibben Excel MVP