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

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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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
>
>

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
> >
> >

format("98.34-","0.00")

returns -98.34

works as long as the first argument is numeric to humans