how to display mainframe negative numbers on excel
 

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

how to display mainframe negative numbers on excel
 

Datatext to columns, click finish


--
Regards,

Peo Sjoblom



"Mario" wrote in message
...
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

how to display mainframe negative numbers on excel
 

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

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


"Mario" wrote:

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

how to display mainframe negative numbers on excel
 

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

Rick


"Teethless mama" wrote in message
...
If you have no negative sign in front then try this:

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


"Mario" wrote:

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

how to display mainframe negative numbers on excel
 

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

how to display mainframe negative numbers on excel
 

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
--
*****
birds of the same feather flock together..



"Mario" wrote:

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

how to display mainframe negative numbers on excel
 

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

--
*****
birds of the same feather flock together..



"driller" wrote:

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
--
*****
birds of the same feather flock together..



"Mario" wrote:

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

how to display mainframe negative numbers on excel
 

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

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

how to display mainframe negative numbers on excel
 

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

On Jun 27, 7:48 pm, Mario wrote:
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