Changing the Negative Sign

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

format("98.34-","0.00")

returns -98.34

works as long as the first argument is numeric to humans