trailing minus
 

hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135

trailing minus
 

Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP


"via135" wrote in message
...
hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135

trailing minus
 

On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

///Make sure Trailing minus for negative numbers is checked ///

hi Biff

there is no option in the advanced text import settings for trailing
minus.!

the options available are "Decimal seperator" & "Thousand seperator"
i'm using ms office 2000 premium.!

-via135

trailing minus
 

Try this macro from Dana DeLouis.

Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub

Select the range of cells in question and run the macro. Post back if you
need help on how to run a macro.

--
Biff
Microsoft Excel MVP


"via135" wrote in message
...
On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

///Make sure Trailing minus for negative numbers is checked ///

hi Biff

there is no option in the advanced text import settings for trailing
minus.!

the options available are "Decimal seperator" & "Thousand seperator"
i'm using ms office 2000 premium.!

-via135

trailing minus
 

Hi,

I use 2000 premium also.
Try it this way.

DataText to Columns
Check Delimited
Click Next
Check Other and type in -
Click Finish

You can also do this with EditReplace
Find what: -
Replace with: Leave this blank
Click Replace all.

HTH
Martin


"via135" wrote in message
...
On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

///Make sure Trailing minus for negative numbers is checked ///

hi Biff

there is no option in the advanced text import settings for trailing
minus.!

the options available are "Decimal seperator" & "Thousand seperator"
i'm using ms office 2000 premium.!

-via135

trailing minus
 

That removes the trailing minus changing the sign of the original value.
It's my understanding that they still want to retain the values as
negatives.

--
Biff
Microsoft Excel MVP


"MartinW" wrote in message
...
Hi,

I use 2000 premium also.
Try it this way.

DataText to Columns
Check Delimited
Click Next
Check Other and type in -
Click Finish

You can also do this with EditReplace
Find what: -
Replace with: Leave this blank
Click Replace all.

HTH
Martin


"via135" wrote in message
...
On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

///Make sure Trailing minus for negative numbers is checked ///

hi Biff

there is no option in the advanced text import settings for trailing
minus.!

the options available are "Decimal seperator" & "Thousand seperator"
i'm using ms office 2000 premium.!

-via135

trailing minus
 

Uhuh, missed that bit!!



"T. Valko" wrote in message
...
That removes the trailing minus changing the sign of the original value.
It's my understanding that they still want to retain the values as
negatives.

--
Biff
Microsoft Excel MVP


"MartinW" wrote in message
...
Hi,

I use 2000 premium also.
Try it this way.

DataText to Columns
Check Delimited
Click Next
Check Other and type in -
Click Finish

You can also do this with EditReplace
Find what: -
Replace with: Leave this blank
Click Replace all.

HTH
Martin


"via135" wrote in message
...
On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in
txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

///Make sure Trailing minus for negative numbers is checked ///

hi Biff

there is no option in the advanced text import settings for trailing
minus.!

the options available are "Decimal seperator" & "Thousand seperator"
i'm using ms office 2000 premium.!

-via135

trailing minus
 

On Nov 25, 2:29 am, "T. Valko" wrote:
Try this macro from Dana DeLouis.

Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub

Select the range of cells in question and run the macro. Post back if you
need help on how to run a macro.

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...



On Nov 24, 11:24 pm, "T. Valko" wrote:
Try this.

Select the range of cells in question
Goto the menu DataText to Columns
Click Next twice
Click Advanced
Make sure Trailing minus for negative numbers is checked
OK
Finish

--
Biff
Microsoft Excel MVP

"via135" wrote in message

...

hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

///Make sure Trailing minus for negative numbers is checked ///

hi Biff

there is no option in the advanced text import settings for trailing
minus.!

the options available are "Decimal seperator" & "Thousand seperator"
i'm using ms office 2000 premium.!

-via135- Hide quoted text -

- Show quoted text -

hi Biff

yes it is..thks..works like a charm!


-via135

trailing minus
 

If there is a mix of postive and negative values
this may be helpful for a one-time-fix:

1) Set the format to an appropriate number format.
2) Put a 1 in an empty cell and copy that cell
3) Select the range to be impacted
4) <edit<paste special....Check: Multiply....Click [OK]

Now, all of the postive values have become numbers.
The negative values are still text

5) Put a -1 in a cell and copy the cell

Note: you'll be in Copy Mode for the next few steps

6) Select the range to be impacted
7) Press the [F5] key...Click: Special
Check: Formulas
Check: Text.....UNcheck the other options
Click [OK]

Now, only the negative values are selected

8) <edit<replace
Find What: -........that's a minus sign
Replace with: (leave this blank)
Click [Replace All]

Press [ESC] twice....to clear the result message and
the edit/replace window.

NOte: you are STILL in Copy Mode
AND the text values are STILL selected

9) <edit<paste special....Check: Multiply....Click [OK]

Now, all of the negative values have become negative numbers.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"via135" wrote in message
...
hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135

trailing minus
 

"via135" wrote in message
...
On Nov 25, 2:29 am, "T. Valko" wrote:
Try this macro from Dana DeLouis.

Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub

Select the range of cells in question and run the macro. Post back if you
need help on how to run a macro.


hi Biff

yes it is..thks..works like a charm!


-via135

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

trailing minus
 

On Nov 25, 7:15 pm, "Ron Coderre"
wrote:
If there is a mix of postive and negative values
this may be helpful for a one-time-fix:

1) Set the format to an appropriate number format.
2) Put a 1 in an empty cell and copy that cell
3) Select the range to be impacted
4) <edit<paste special....Check: Multiply....Click [OK]

Now, all of the postive values have become numbers.
The negative values are still text

5) Put a -1 in a cell and copy the cell

Note: you'll be in Copy Mode for the next few steps

6) Select the range to be impacted
7) Press the [F5] key...Click: Special
Check: Formulas
Check: Text.....UNcheck the other options
Click [OK]

Now, only the negative values are selected

8) <edit<replace
Find What: -........that's a minus sign
Replace with: (leave this blank)
Click [Replace All]

Press [ESC] twice....to clear the result message and
the edit/replace window.

NOte: you are STILL in Copy Mode
AND the text values are STILL selected

9) <edit<paste special....Check: Multiply....Click [OK]

Now, all of the negative values have become negative numbers.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

###7) Press the [F5] key...Click: Special
Check: Formulas
Check: Text.....UNcheck the other options
Click [OK]
Now, only the negative values are selected###

sorry ron..!
i'm getting error "no cells were found"

-via135