Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
Using copy paste from AS400 to excel spreadsheet negative numbers do not read
negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
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 wilson@irco wrote: Using copy paste from AS400 to excel spreadsheet negative numbers do not read negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
Hi
I use a free add-in from www.asap-utilities.com for this job. There are loads of excellent (and useful) functions in the pack. Andy. "wilson@irco" wrote in message ... Using copy paste from AS400 to excel spreadsheet negative numbers do not read negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
I suspect the AS/400 data is formatted as TEXT in Excel so you will have to
remove the "-" (minus) and then convert to a number. if A1=1234- Then in B1 put: =SUBSTITUTE(A1,"-","")*-1 B1=-1234 If the data is in one column, you insert a helper column and copy a formula down and then delete original column. To cater for positive numbers, use: =IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1) Test first! "wilson@irco" wrote: Using copy paste from AS400 to excel spreadsheet negative numbers do not read negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
It works...thank you for that and the link for macro newbies. :)
"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 wilson@irco wrote: Using copy paste from AS400 to excel spreadsheet negative numbers do not read negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
Try DataText to ColumnsNextNextAdvanced.
Checkmark "trailing minus for negative numbers". Gord Dibben MS Excel MVP On Thu, 9 Mar 2006 11:59:02 -0800, wilson@irco wrote: Using copy paste from AS400 to excel spreadsheet negative numbers do not read negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
flip negative sign
Great. Thank you...you wouldn't happen to have the solution or at least an
attempt at the subtotal loop question proposed on 3/9 also. No one has responded yet. I might need to dangle a carrot out there or something. Surely it's doable. Thanks again. "Gord Dibben" wrote: Try DataText to ColumnsNextNextAdvanced. Checkmark "trailing minus for negative numbers". Gord Dibben MS Excel MVP On Thu, 9 Mar 2006 11:59:02 -0800, wilson@irco wrote: Using copy paste from AS400 to excel spreadsheet negative numbers do not read negative because the negative sign remains on the right side of the number in excel. Is there an easy way to flip the negative sign from the right side of the number to the left side? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EQUAL SIGN | Excel Discussion (Misc queries) | |||
Negative Values Only | Excel Discussion (Misc queries) | |||
How can i put a negative sign on a range of selected cells? | Excel Discussion (Misc queries) | |||
FORMULA TO ADD POSITIVE AND NEGATIVE NUMBERS, REGARDLESS OF SIGN | Excel Discussion (Misc queries) | |||
Automatically put negative sign in cells with positive numeric dat | Excel Discussion (Misc queries) |