Numbers

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
 
H

Harald Staff

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.
 
G

Gord Dibben

Select your range of data.

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

Gord Dibben XL2002
 
H

Harald Staff

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.
 
D

Dave Peterson

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).
 
H

Harald Staff

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.
 
G

Gord Dibben

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
 
Top