Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 . |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import external data from changing file name | Excel Worksheet Functions | |||
Import Data into same cell | Excel Discussion (Misc queries) | |||
Import Data: on insert, shift data down and not right | Excel Discussion (Misc queries) | |||
Automatic Data Import | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |