Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Negative Sign
I have a file that I import into Excel. The negative sign on some of the
amounts is at the end of the number. Example: 2,778.69-. Excel dosn't know that this is a negative amount. Is there an easy way to move the sign to the font of the number or to reformat the cell to show that it is a negative amount? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Negative Sign
Dim s as String
Dim cell as Range for each cell in selection.specialCells(xlConstants,xlTextValues) s = trim(cell) if isnumeric(s) cell.value = cdbl(s) end if Next -- Regards, Tom Ogilvy "tedd13" wrote: I have a file that I import into Excel. The negative sign on some of the amounts is at the end of the number. Example: 2,778.69-. Excel dosn't know that this is a negative amount. Is there an easy way to move the sign to the font of the number or to reformat the cell to show that it is a negative amount? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Negative Sign
You could transform it into an adjacent column
=IF(RIGHT(A1)="-",-1*LEFT(A1,LEN(A1)-1),A1) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "tedd13" wrote in message ... I have a file that I import into Excel. The negative sign on some of the amounts is at the end of the number. Example: 2,778.69-. Excel dosn't know that this is a negative amount. Is there an easy way to move the sign to the font of the number or to reformat the cell to show that it is a negative amount? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Negative Sign
Tom,
That code worked. However, it errors out if it there are no negative signs in the column I am looking at. I have six columns that may or may not have negative signs. Any ideas about how to code around that? Thanks "tedd13" wrote: I have a file that I import into Excel. The negative sign on some of the amounts is at the end of the number. Example: 2,778.69-. Excel dosn't know that this is a negative amount. Is there an easy way to move the sign to the font of the number or to reformat the cell to show that it is a negative amount? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Negative Sign
Dim s as String
Dim cell as Range, rng as Range On Error Resume Next set rng = selection.specialCells(xlConstants,xlTextValues) On Error goto 0 if not rng is nothing then for each cell in s = trim(cell) if isnumeric(s) cell.value = cdbl(s) end if Next end If -- Regards, Tom Ogilvy "tedd13" wrote: Tom, That code worked. However, it errors out if it there are no negative signs in the column I am looking at. I have six columns that may or may not have negative signs. Any ideas about how to code around that? Thanks "tedd13" wrote: I have a file that I import into Excel. The negative sign on some of the amounts is at the end of the number. Example: 2,778.69-. Excel dosn't know that this is a negative amount. Is there an easy way to move the sign to the font of the number or to reformat the cell to show that it is a negative amount? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Negative Sign
format("98.34-","0.00") returns -98.34 works as long as the first argument is numeric to humans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If sign of sum is negative change to positive | Excel Worksheet Functions | |||
Changing color of negative bar when using Invert if negative | Charts and Charting in Excel | |||
Ending Negative Sign | Excel Worksheet Functions | |||
flip negative sign | Excel Discussion (Misc queries) | |||
Deleting a negative sign from an amount in a list (not trailing negative) | Excel Programming |