negative numbers
 

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

negative numbers
 

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaxstraww wrote:

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

--

Dave Peterson

negative numbers
 

Other than the macro isn't there just a setting in Excel I can manipulate? I
have had other installs of Office that it converted for me. Issue now is I
had a HD failure and a reinstall of Office. Now I'm having this problem.

"Dave Peterson" wrote:

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaxstraww wrote:

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

--

Dave Peterson

negative numbers
 

I don't know of a setting for this.

If you were importing it from a text file (or running each column through
data|text to columns) and are using xl2002+, you could specify that the fields
use a trailing minus for negative numbers.

You could use a bunch of helper cells with formulas that did the same kind of
thing. But I would think updating the data in place would be worth the effort
of a macro.



jaxstraww wrote:

Other than the macro isn't there just a setting in Excel I can manipulate? I
have had other installs of Office that it converted for me. Issue now is I
had a HD failure and a reinstall of Office. Now I'm having this problem.

"Dave Peterson" wrote:

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaxstraww wrote:

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

--

Dave Peterson


--

Dave Peterson

negative numbers
 

DataText to ColumnsNextNextAdvancedCheckmark in "Trailing minus for
negative numbers"

Finish and format to red brackets.


Gord Dibben MS Excel MVP

On Fri, 1 Sep 2006 11:16:02 -0700, jaxstraww
wrote:

Other than the macro isn't there just a setting in Excel I can manipulate? I
have had other installs of Office that it converted for me. Issue now is I
had a HD failure and a reinstall of Office. Now I'm having this problem.

"Dave Peterson" wrote:

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaxstraww wrote:

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

--

Dave Peterson

negative numbers
 

Missed this post Dave so posted the text to columns info an hour later<g

Apologies.


Gord



On Fri, 01 Sep 2006 14:03:29 -0500, Dave Peterson
wrote:

I don't know of a setting for this.

If you were importing it from a text file (or running each column through
data|text to columns) and are using xl2002+, you could specify that the fields
use a trailing minus for negative numbers.

You could use a bunch of helper cells with formulas that did the same kind of
thing. But I would think updating the data in place would be worth the effort
of a macro.



jaxstraww wrote:

Other than the macro isn't there just a setting in Excel I can manipulate? I
have had other installs of Office that it converted for me. Issue now is I
had a HD failure and a reinstall of Office. Now I'm having this problem.

"Dave Peterson" wrote:

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaxstraww wrote:

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

--

Dave Peterson

negative numbers
 

Dave--I got it to work after I made my last post. Wasn't to familiar with
creating a macro that would always run when Excel is open. It's an extra step
from where I was but much easier than adjusting all the cells with the bad
data. Thanks again.

"Dave Peterson" wrote:

I don't know of a setting for this.

If you were importing it from a text file (or running each column through
data|text to columns) and are using xl2002+, you could specify that the fields
use a trailing minus for negative numbers.

You could use a bunch of helper cells with formulas that did the same kind of
thing. But I would think updating the data in place would be worth the effort
of a macro.



jaxstraww wrote:

Other than the macro isn't there just a setting in Excel I can manipulate? I
have had other installs of Office that it converted for me. Issue now is I
had a HD failure and a reinstall of Office. Now I'm having this problem.

"Dave Peterson" wrote:

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jaxstraww wrote:

I'm cutting and pasting out of a program to an Excel worksheet. The negative
numbers in the program I'm copying from look like 202.50- for a negative
number. The positive numbers 202.50 look fine when pasted into an excel
worksheet. When I paste this type of number into the excel worksheet I need
it to become in red ($202.50)
Currently it pastes 202.50- and I need to go in and delete the - on the
right side of the number and place it on the left side of the number to
convert. Simply I need Excel to convert to a negative number if the - is on
the right side of the number.

Thanks.

--

Dave Peterson


--

Dave Peterson