Numbers

  • Thread starter Thread starter Mry Alice London
  • Start date Start date
M

Mry Alice London

When transferring data from my AS400 to Excel, the
negative numbers appear on the AS400 with the minus sign
at the back of the number. Ex: 123-
Is there a way to make excel recognize this as a negative
number through the customize option--or any way at all?

Thank you
 
Hi

Select the troublesome cell range and run this little macro:

Sub ConvertThem()
Dim C As Range
For Each C In Intersect(Selection, _
ActiveSheet.UsedRange)
If Not C.HasFormula Then
If IsNumeric(C.Value) Then
C.Value = C.Value * 1
End If
End If
Next
End Sub

If you select too much (whole columns or something) it shouldn't create damage. But don't
save until you're sure.
 
Select your range of data.

Data>Text to Columns>Next>Next>Finish.

Gord Dibben XL2002
 
Select your range of data.

Data>Text to Columns>Next>Next>Finish.
Click to expand...

Neat trick, Gord. It would be very far down on a "I have to try that" list.

Best wishes Harald
Followup to newsgroup only please.
 
This worked for me in xl2002, but I think xl2002 was the first to support
"trailing minus for negative numbers" (under the advanced button on step 3 of
the wizard--and it looks like the default is to have this enabled).
 
Data>Text to Columns>Next>Next>Finish.
This worked for me in xl2002, but I think xl2002 was the first to support
"trailing minus for negative numbers" (under the advanced button on step 3 of
the wizard--and it looks like the default is to have this enabled).
Click to expand...

You're right Dave, no good in Excel 2000.
 
Works only if you have, in step 3, "Advanced">"Trailing minus sign for
negative numbers" checked. This is checked by default.

Don't believe XL97 has this. Will check later.

Gord
 
Back
Top