Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"find and replace" negative numbers | Excel Worksheet Functions | |||
How do I replace negative numbers with zero in Excel. | Excel Worksheet Functions | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
Add sequences of positive then negative numbers | Excel Discussion (Misc queries) | |||
How to change a series of positive numbers to negative numbers | Excel Worksheet Functions |