Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
Everyday I have to copy and paste data from a program to an excel
spreadsheet. Positive numbers come in fine, negative numbers don't. They align to the left of the cell with the negative sign to the right of the numbers. Currently I: F2, backspace, home, put in the - sign and enter to the next cell where I repeat this approximately 80 times. I have tried setting up a macro, a scenerio, and a what if function, but with no luck on any of them. The macro was my best guess, but it saves the number along with the steps, so everyday the numbers revert back to the ones saved in the macro not what they should be. Does anyone have a solution for quickly changing the format of these numbers after they are pasted in? -- Thank you, Lisa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
do you define the cells you are copying into as numeric before you paste?
"Lisa" wrote: Everyday I have to copy and paste data from a program to an excel spreadsheet. Positive numbers come in fine, negative numbers don't. They align to the left of the cell with the negative sign to the right of the numbers. Currently I: F2, backspace, home, put in the - sign and enter to the next cell where I repeat this approximately 80 times. I have tried setting up a macro, a scenerio, and a what if function, but with no luck on any of them. The macro was my best guess, but it saves the number along with the steps, so everyday the numbers revert back to the ones saved in the macro not what they should be. Does anyone have a solution for quickly changing the format of these numbers after they are pasted in? -- Thank you, Lisa |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
Select the range.
DataText to ColumnsNextNextAdvanced. Checkmark in "trailing minus sign for negative numbers"Finish. A macro if you wish. Sub Negsignleft() Dim cell As Range Dim rng As Range ''move minus sign from right to left on entire worksheet On Error Resume Next Set rng = ActiveSheet.Cells. _ SpecialCells(xlCellTypeConstants, xlTextValues) On Error GoTo 0 For Each cell In rng If IsNumeric(cell.Value) Then cell.Value = CDbl(cell.Value) End If Next cell End Sub Gord Dibben MS Excel MVP On Thu, 28 Sep 2006 09:41:03 -0700, Lisa wrote: Everyday I have to copy and paste data from a program to an excel spreadsheet. Positive numbers come in fine, negative numbers don't. They align to the left of the cell with the negative sign to the right of the numbers. Currently I: F2, backspace, home, put in the - sign and enter to the next cell where I repeat this approximately 80 times. I have tried setting up a macro, a scenerio, and a what if function, but with no luck on any of them. The macro was my best guess, but it saves the number along with the steps, so everyday the numbers revert back to the ones saved in the macro not what they should be. Does anyone have a solution for quickly changing the format of these numbers after they are pasted in? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
Yes, the cells are set up to have two decimal places, a comma, and show
negative numbers in red with brackets. -- Thank you, Lisa "tim m" wrote: do you define the cells you are copying into as numeric before you paste? "Lisa" wrote: Everyday I have to copy and paste data from a program to an excel spreadsheet. Positive numbers come in fine, negative numbers don't. They align to the left of the cell with the negative sign to the right of the numbers. Currently I: F2, backspace, home, put in the - sign and enter to the next cell where I repeat this approximately 80 times. I have tried setting up a macro, a scenerio, and a what if function, but with no luck on any of them. The macro was my best guess, but it saves the number along with the steps, so everyday the numbers revert back to the ones saved in the macro not what they should be. Does anyone have a solution for quickly changing the format of these numbers after they are pasted in? -- Thank you, Lisa |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
Thank you. This looked so easy, unfortunately when I went to the advanced
step, my only two choices were decimal separator or thousands separator with drop down answers. There was nothing that said "trailing minus sign for negative numbers" I tried both with delimited text and fixed width. Work computers have excel 2000... would this be the issue? -- Thank you, Lisa "Gord Dibben" wrote: Select the range. DataText to ColumnsNextNextAdvanced. Checkmark in "trailing minus sign for negative numbers"Finish. A macro if you wish. Sub Negsignleft() Dim cell As Range Dim rng As Range ''move minus sign from right to left on entire worksheet On Error Resume Next Set rng = ActiveSheet.Cells. _ SpecialCells(xlCellTypeConstants, xlTextValues) On Error GoTo 0 For Each cell In rng If IsNumeric(cell.Value) Then cell.Value = CDbl(cell.Value) End If Next cell End Sub Gord Dibben MS Excel MVP On Thu, 28 Sep 2006 09:41:03 -0700, Lisa wrote: Everyday I have to copy and paste data from a program to an excel spreadsheet. Positive numbers come in fine, negative numbers don't. They align to the left of the cell with the negative sign to the right of the numbers. Currently I: F2, backspace, home, put in the - sign and enter to the next cell where I repeat this approximately 80 times. I have tried setting up a macro, a scenerio, and a what if function, but with no luck on any of them. The macro was my best guess, but it saves the number along with the steps, so everyday the numbers revert back to the ones saved in the macro not what they should be. Does anyone have a solution for quickly changing the format of these numbers after they are pasted in? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
Apologies for not stating the this feature under Text to Columns was introduced
in Excel 2002. The macro will work in all versions. Gord On Thu, 28 Sep 2006 11:45:01 -0700, Lisa wrote: Thank you. This looked so easy, unfortunately when I went to the advanced step, my only two choices were decimal separator or thousands separator with drop down answers. There was nothing that said "trailing minus sign for negative numbers" I tried both with delimited text and fixed width. Work computers have excel 2000... would this be the issue? Gord Dibben MS Excel MVP |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
You have the undying gratitude from everyone in my department! The macro
works perfectly. THANK YOU THANK YOU THANK YOU!!! -- Thank you, Lisa "Gord Dibben" wrote: Apologies for not stating the this feature under Text to Columns was introduced in Excel 2002. The macro will work in all versions. Gord On Thu, 28 Sep 2006 11:45:01 -0700, Lisa wrote: Thank you. This looked so easy, unfortunately when I went to the advanced step, my only two choices were decimal separator or thousands separator with drop down answers. There was nothing that said "trailing minus sign for negative numbers" I tried both with delimited text and fixed width. Work computers have excel 2000... would this be the issue? Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing 1234.00- to -1234.00 automatically
Is that a yes or a no<g
Thanks for the feedback, Gord On Thu, 28 Sep 2006 13:06:01 -0700, Lisa wrote: You have the undying gratitude from everyone in my department! The macro works perfectly. THANK YOU THANK YOU THANK YOU!!! Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating and changing cells automatically | Excel Discussion (Misc queries) | |||
Automatically Changing Functions | Excel Discussion (Misc queries) | |||
Need to stop formuls from automatically changing | Excel Discussion (Misc queries) | |||
Automatically changing colors | Excel Discussion (Misc queries) | |||
Excel Changing Numbers Automatically | Excel Discussion (Misc queries) |