Changing data from imported .txt file

[Guest]

When i import data from an old (dos based) program negative numbers appear
with the minus behind the number. Does anyone have any idea how i can change
them all from 1,234.56- to -1,234.56 so Excel will recognise them? Changing
them by hand takes forever. Thank you.

 

[Dave Peterson]

If you're using xl2002+ and are using File|Open to open a text file, you'll see
a wizard popup.

On step 3 of that wizard, there's an advanced button.
Click it.

There's an option to treat trailing minus signs as negative numbers.

If you already have the data in the worksheet, you can use...

Dana DeLouis posted this:


Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, (e-mail address removed)
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm