Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative number format
I have a report uploaded to excel from a particular program used here at
work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. I have been manually moving the minus signs to the beginning in each cell which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing these numbers to globally change them at once? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative number format
If first data point is in A2:
=IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)) Formula checks for minus sign, if found, create negative number, if not found, a positive number. Copy formula down/across as desired. Then at end, I'd recommend copying the cells with formula, and right-click/paste special "values" to where ever you want to data to ultimatley go. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "jpriddy" wrote: I have a report uploaded to excel from a particular program used here at work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. I have been manually moving the minus signs to the beginning in each cell which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing these numbers to globally change them at once? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative number format
Hi Jpriddy,
Assuming your data in cell A1 you can put following formula in cell A2, =RIGHT(A1,1)&REPLACE(A1,LEN(A1),2,"") H S Shastri ==++++++++++++++++++++++++++++++++++++++++++++++++ +++ "jpriddy" wrote: I have a report uploaded to excel from a particular program used here at work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. I have been manually moving the minus signs to the beginning in each cell which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing these numbers to globally change them at once? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative number format
This subrountine will make the needed corrections
Sub tryme() For Each mycell In Selection If Right(mycell, 1) = "-" Then If Left(mycell, 1) = "$" Then x = Val(Mid(mycell, 2, Len(mycell))) x = -x mycell.Value = FormatCurrency(x, 2) Else x = Val(mycell) mycell.Value = -x End If End If Next best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "jpriddy" wrote in message ... I have a report uploaded to excel from a particular program used here at work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. I have been manually moving the minus signs to the beginning in each cell which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing these numbers to globally change them at once? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative number format
Maybe I'm not doing this correctly...The first data point is cell J2, with
6,476.52- When I enter your formula, changing to J2, i get an error relating to a circular reference. "Luke M" wrote: If first data point is in A2: =IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)) Formula checks for minus sign, if found, create negative number, if not found, a positive number. Copy formula down/across as desired. Then at end, I'd recommend copying the cells with formula, and right-click/paste special "values" to where ever you want to data to ultimatley go. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "jpriddy" wrote: I have a report uploaded to excel from a particular program used here at work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. I have been manually moving the minus signs to the beginning in each cell which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing these numbers to globally change them at once? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative number format
Don't put the formula in cell J2. You have to put it in a seperate cell.
*Circular reference error occurs when the formula refers to the value in the cell your formula is in. Since the value is calculated after formula, and formula needs value, this is called a circular refernce.* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "jpriddy" wrote: Maybe I'm not doing this correctly...The first data point is cell J2, with 6,476.52- When I enter your formula, changing to J2, i get an error relating to a circular reference. "Luke M" wrote: If first data point is in A2: =IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)) Formula checks for minus sign, if found, create negative number, if not found, a positive number. Copy formula down/across as desired. Then at end, I'd recommend copying the cells with formula, and right-click/paste special "values" to where ever you want to data to ultimatley go. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "jpriddy" wrote: I have a report uploaded to excel from a particular program used here at work. In that program, negative numbers have the format of $###.00-, with the minus sign at the end instead of the beginning. When the data comes over to Excel the minus sign is still at the end of the number. I have been manually moving the minus signs to the beginning in each cell which is very time consuming. Is there a formula I can use in the Cell, Column and/or Worksheet containing these numbers to globally change them at once? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
negative number format | New Users to Excel | |||
Format cells to change a number from a positive to negative | Excel Worksheet Functions | |||
Custom number format codes for negative numbers | Excel Discussion (Misc queries) | |||
How do I format a negative number in parenthesis? | Excel Discussion (Misc queries) | |||
Negative Number Format () Not available | Excel Discussion (Misc queries) |