Import accounts data from AS400 system, how do I change 10- to (1.
 

The negative value in the AS400 system appears with the negative symbol after
the value, so when imported into excell it does not calculate it as a
negative value. How do I change that?

Hi Wallaby,

A1 = 1234.01-

you need an extra column with this formula
=IF(right(a1,1)="-", left(a1, len(a1)-1)*(-1), a1)

The best would be to make your as400 deliver the "normal" number
format, that you can use without extra formulas. check any export
settings on the as400!

see ya

arno

wrote:
The negative value in the AS400 system appears with the negative
symbol after the value, so when imported into excell it does not
calculate it as a negative value. How do I change that?

Try a simple replace. Select the data, press Ctrl+H, and:
Find What: -
Replace With: <leave empty

HTH
Jason
Atlanta, GA

-----Original Message-----
The negative value in the AS400 system appears with the
negative symbol after
the value, so when imported into excell it does not
calculate it as a
negative value. How do I change that?
.

Hi Jason,

Replace With: <leave empty

cool, this will turn a list of positive and negative values into a list
of absolute values. Why bother with those bloody minuses, just throw
them away!

arno

Thanx Arno and Jason

Arno your formula give me an error message "value" once typed in. I assume
that it must be the formatting. when I type in a number 15- it converts, but
with the number as 21.561,00- no luck.

"arno" wrote:

Hi Jason,

Replace With: <leave empty

cool, this will turn a list of positive and negative values into a list
of absolute values. Why bother with those bloody minuses, just throw
them away!

arno

Hi Wallaby,

Arno your formula give me an error message "value" once typed in. I
assume that it must be the formatting. when I type in a number 15- it
converts, but with the number as 21.561,00- no luck.

try
=IF(right(a1,1)="-", left(value(a1), len(a1)-1)*(-1), a1)

if this does not work post some sample data of the as400 and tell me
what the result of
=left(a1, len(a1)-1)
exactly is. (It should be a number, if it is a text you'll get #value.)
Maybe we have to trim or use some other text-functions to make it work.

arno

I don't know what I was thinking. Scratch this.

-----Original Message-----
Hi Jason,

Replace With: <leave empty

cool, this will turn a list of positive and negative
values into a list
of absolute values. Why bother with those bloody
minuses, just throw
them away!

arno

.

DataText to ColumnsNextNextAdvanced. Checkmark "trailing minuses" and
Finish.


Gord Dibben Excel MVP

On Thu, 14 Apr 2005 02:14:01 -0700, "Wallaby"
wrote:

The negative value in the AS400 system appears with the negative symbol after
the value, so when imported into excell it does not calculate it as a
negative value. How do I change that?

Check out another answer to your first post.

Much easier than formulas.


Gord Dibben Excel MVP

On Thu, 14 Apr 2005 07:21:06 -0700, "Wallaby"
wrote:

Thanx Arno and Jason

Arno your formula give me an error message "value" once typed in. I assume
that it must be the formatting. when I type in a number 15- it converts, but
with the number as 21.561,00- no luck.

"arno" wrote:

Hi Jason,

Replace With: <leave empty

cool, this will turn a list of positive and negative values into a list
of absolute values. Why bother with those bloody minuses, just throw
them away!

arno

Hi Gord,
DataText to ColumnsNextNextAdvanced. Checkmark "trailing
minuses" and Finish.

Great, this works in my XL 2002. Is this feature also available in XL
2000 and XL 97?

arno