how to display mainframe negative numbers on excel

[Mario]

Hi;
i have some negative numbers from a mainframe report and whant to used in a
excel worksheet, how can i do that, because excel uses the negative sign on
the left.
i.e data from mainframe:
0.128761-
1.234510
etc

 

[Peo Sjoblom]

Data>text to columns, click finish

 

[Teethless mama]

If you have no negative sign in front then try this:

ctrl-H >> Find what: - >> Replace with: "leave blank" >> Replace All

 

[Rick Rothstein \(MVP - VB\)]

Won't that take the intended negative value and make it positive?

Rick

 

[Rick Rothstein \(MVP - VB\)]

i have some negative numbers from a mainframe report and whant

to used in a excel worksheet, how can i do that, because excel uses
the negative sign on the left.
i.e data from mainframe:
0.128761-
1.234510
etc

Would this off-the-top-of-my-head macro possibly be useful?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo FixEvents
If Right$(Target.Text, 1) = "-" Then
Application.EnableEvents = False
Target.Value = "-" & Left$(Target.Text, Len(Target.Text) - 1)
Application.EnableEvents = True
End If
Exit Sub
FixEvents:
Application.EnableEvents = True
End Sub


Rick

 

[driller]

if a helper column will fit u...try this formula
e.g.
Column A holds the data....*hidden characters or spaces b/w numbers not
assumed on sample*

on B1
=IF(RIGHT(TRIM(A1),1)="-",-1,1)*LEFT(TRIM(A1),LEN(TRIM(A1))-(RIGHT(TRIM(A1),1)="-")*1)

this might give a fresh raw numbers for excel use.

regards,
driller

 

[driller]

on B1
=IF(RIGHT(TRIM(A1),1)="-",-1,1)*LEFT(TRIM(A1),LEN(TRIM(A1))-(RIGHT(TRIM(A1),1)="-"))

 

[Rick Rothstein \(MVP - VB\)]

i have some negative numbers from a mainframe report and whant

Would this off-the-top-of-my-head macro possibly be useful?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo FixEvents
If Right$(Target.Text, 1) = "-" Then
Application.EnableEvents = False
Target.Value = "-" & Left$(Target.Text, Len(Target.Text) - 1)
Application.EnableEvents = True
End If
Exit Sub
FixEvents:
Application.EnableEvents = True
End Sub

Probably should test to make sure we really have a number before we change
it. Perhaps something like this....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Number As Variant
On Error GoTo FixEvents
If Right$(Target.Text, 1) = "-" Then
Number = Left$(Target.Text, Len(Target.Text) - 1)
If IsNumeric(Number) Then
Application.EnableEvents = False
Target.Value = "-" & Number
Application.EnableEvents = True
End If
End If
Exit Sub
FixEvents:
Application.EnableEvents = True
End Sub


Rick

 

[Pete_UK]

I presume that the negative numbers are treated by Excel as text,
whereas positive numbers are treated as numeric. If so, and assuming
the data is in column A, you could have a formula like:

=IF(ISNUMBER(A1),A1,-VALUE(LEFT(A1,LEN(A1)-1)))

Copy this down the column for as many rows as you have items in column
A. You can then fix the values in this column by highlighting it,
click <copy>, then Edit | Paste Special | Values (check) | OK then
<Esc> or <Enter>. Then you can delete the original data in column A.

Hope this helps.

Pete