trailing minus

[via135]

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

 

[T. Valko]

Try this.

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

 

[via135]

Try this.

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

--
Biff
Microsoft Excel MVP









- 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

 

[T. Valko]

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.

 

[MartinW]

Hi,

I use 2000 premium also.
Try it this way.

Data>Text to Columns
Check Delimited
Click Next
Check Other and type in -
Click Finish

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

HTH
Martin

 

[T. Valko]

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.

 

[MartinW]

Uhuh, missed that bit!!

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.

 

[via135]

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











- Show quoted text -

hi Biff

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


-via135

 

[Ron Coderre]

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)

 

[T. Valko]

hi Biff

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


-via135

You're welcome. Thanks for the feedback!

 

[via135]

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)

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