Formatting negative numbers

T

Timster

I have a text file that gets imported into Excel on a
regular basis. The negative numbers come into the
spreadsheet in this format: 4000.00- and I want to know
if I can write a macro to change all those instances
to: -4000.00, moving the negative sign to the proper
place. Anyone know if that can be done. In the past we
used an old dos application to do that prior to importing
it into Excel, but I want to get away from that if we
could and just let Excel do it. There are other places
in the file that contain text with dashes in it, so I
want it only to find the numbers with negative symbols,
or dashes in them!!! Thanks in advance.
 
C

Charlie

Here is something I picked up from
http://www.mcgimpsey.com/excel/postfixnegatives.html
previously posted in this newsgroup.

Public Sub ConvertTrailingMinuses()
Dim cell As Range

On Error Resume Next
For Each cell In ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With cell
If IsNumeric(.Value) Then _
.Value = CDbl(.Value)
End With
Next cell
On Error GoTo 0
End Sub

Charlie O'Neill
 
Top