adding to an entire worksheet

  • Thread starter Thread starter Steve Chatham
  • Start date Start date
S

Steve Chatham

I have a worksheet that is exported from another source, and the signs in it
are trailing.

I need to get the signs to be proper for Excel to handle them. Currently,
they're numbers like 123 and 99-.

I thought that you could add a signed number, like a +0 to the contents of a
spreadsheet, and make it come out right, but cannot find out how to do that
in the version of excel I am using.

Is there a way to convert this on import, or is there a macro or other set
of menu commands that Microsoft seemingly has hidden from me?

Thanks

S
 
If you're using xl2002, there's an option under the data|Import wizard that says
to treat trailing minuses as negatives.

(It's under the advanced tab under step 3)

This was posted by Dana DeLouis:

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
 
If you're using an older version of XL, and you wish to use a function,
since you did cross-post to the Functions Group also, you could try this in
an adjoining column and copy down:

=IF(ISERROR(A1*1),LEFT(A1,LEN(A1)-1)*-1,A1)

--


HTH,

RD
========================================
Please keep all correspondence within the Group, so all may benefit!
========================================
 
Back
Top