experchange > excel.* > excel.misc

Heather (04-21-05, 06:39 PM)
When I import to excel from our accounting system, negative numbers show the
minus sign at the right e.g. 100- instead of -100 or (100) Excel reads this
as text and I have to go through the list manually inserting a minus sign at
the left and deleting the minus sign from the right.

Is there any easier way to do this? The numbers are not a fixed length, so
the - could be the third or the thirteenth character in the cell.
Heather (04-21-05, 06:45 PM)
Found it! Thanks PJB
=IF(RIGHT(A1,1)="-",-SUBSTITUTE(A1,"-",""),A1)

"Heather" wrote:
[..]
Ron Rosenfeld (04-21-05, 06:52 PM)
On Thu, 21 Apr 2005 09:39:26 -0700, "Heather"
<Heather> wrote:

>When I import to excel from our accounting system, negative numbers show the
>minus sign at the right e.g. 100- instead of -100 or (100) Excel reads this
>as text and I have to go through the list manually inserting a minus sign at
>the left and deleting the minus sign from the right.
>Is there any easier way to do this? The numbers are not a fixed length, so
>the - could be the third or the thirteenth character in the cell.


What version of Excel do you have?

Later versions (2002+, possibly earlier) can do this automatically -- easiest
if your data to be converted is in columns.

Select the relevant column (one column at a time).

From the main menu, select Data/Text to Columns
Next
Next
Advanced: Ensure "Trailing minus for negative numbers" is SELECTED.
OK
Finish

If you have an earlier version, or a complicated distribution of numbers with
trailing negatives, post back and we can use a VBA routine.

--ron
Similar Threads