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

Data>text to columns, click finish


--
Regards,

Peo Sjoblom



"Mario" <(E-Mail Removed)> wrote in message
news:137600DD-2AC1-4664-BC68-(E-Mail Removed)...
> 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
>
>

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

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

Rick


"Teethless mama" <(E-Mail Removed)> wrote in message
news:3C27A19F-C0D3-4F09-8B88-(E-Mail Removed)...
> 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
>>
>>

> 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

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

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

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

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 <M...@discussions.microsoft.com> 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