negative numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Dana DeLouis posted this:


Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, (e-mail address removed)
' = = = = = = = = = = = = = = = =
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
 
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.
 
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.
 
Data>Text to Columns>Next>Next>Advanced>Checkmark in "Trailing minus for
negative numbers"

Finish and format to red brackets.


Gord Dibben MS Excel MVP
 
Missed this post Dave so posted the text to columns info an hour later<g>

Apologies.


Gord
 
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.
 
Back
Top