how to convert text with trailing minus sign to number

[Guest]

I have downloaded a text report (from SAP) that puts the "minus" sign behind
the numerals for negative number.
These negative numbers are converted to "text" under normal conversion.
Is there a way to correctly convert these number?
Thanks in advance.

 

[Biff]

Hi!

You might be able to use the Text To Columns feature for
this.

Select the range of numbers to convert.
Goto Data>Text To Columns.
Select Delimited
Click Next
For the delimiter, select Other and in the box enter -
Click Finish

Biff

 

[Patti]

I don't remember where I found this macro -- I wish I did
so I could give proper credit! This is a gem and has
saved me much time!

Sub MoveMinus()
On Error Resume Next
Dim cel As Range
Dim myVar As Range
Set myVar = Selection

For Each cel In myVar
If Right((Trim(cel)), 1) = "-" Then
cel.Value = cel.Value * 1
End If
Next

With myVar
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.Columns.AutoFit
End With
End Sub

 

[Dave Peterson]

This feature was added in xl2002 (just in case the OP used xl2k or older).

 

[Dave Peterson]

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