Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding to an entire worksheet
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding to an entire worksheet
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, ' = = = = = = = = = = = = = = = = 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 Steve Chatham wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding to an entire worksheet
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! ======================================== "Steve Chatham" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Headers and Footer to an Entire Workbook?? | Excel Discussion (Misc queries) | |||
How to reference entire worksheet in new worksheet | Excel Worksheet Functions | |||
converting entire worksheet to pdf | Excel Discussion (Misc queries) | |||
Sum only adding rows on page instead of entire spreadsheet - help | Excel Discussion (Misc queries) | |||
adding ".jpg" to an entire column | Excel Discussion (Misc queries) |