data that includes a negative sign on the left

[mikeism]

When I import financial data that includes a negative sign on the left, even if I designate the column as currency or number format, Planmaker imports the data as text. For example, characters like these... -$1015.00, -$105.57, -$722.43 are imported as text. I tried the Format / Cell options to specify currency and tried number as well but Planmaker failed to convert; still treats the data as text and thus I can't perform math operations. Also tried to cut the data and paste back in as unformated text; that works on text without a minus sign but not on text with a minus sign to the left. What is the process for converting data like this to currncy or number format? Why doesn't PlanMaker convert to number format when you configure the import dialog box to do so?

[raitis.veksejs]

To test your described scenario, we would like to ask you a description of what you do step-by-step and an exact sample file. If it contains sensitive data you can send it us to e-mail forum [at] softmaker.com and include also link to this forum.

[Woody44]

It should not display negative values as "-$1015.00" if the cell is set to display numbers as currency. It should display as "($1015.00). Conversely, if the cell is set for "Number," it should not display a dollar sign.

How are you entering the values -- exactly?

[mikeism]

Hi Woody... I'm not entering these numbers manually. I'm importing them. As far as I can tell, it isn't possible to manually enter numbers in the format I described. To summarize:

-$57.00 fails
$-57.00 is ok

My import file from my bank can't be changed and Planmaker fails to recognize it as number when imported. The VALUE function will not convert it either.

[mikeism]

As clarification to my prior post...

1) This number: -$57.00 can NOT BE ENTERED MANUALLY. When I try to enter it manually Planmaker displays an error and will not accept it.

2) This number: -$57.00 can NOT BE IMPORTED as a number. Planmaker treats it only as text. Even the VALUE function can't convert it to number or currency format.

$-57.00 is ok. -$57.00 doesn't work.

[Woody44]

Hi Woody... I'm not entering these numbers manually. I'm importing them. As far as I can tell, it isn't possible to manually enter numbers in the format I described. To summarize:

-$57.00 fails
$-57.00 is ok

My import file from my bank can't be changed and Planmaker fails to recognize it as number when imported. The VALUE function will not convert it either.

Okay, now I understand the problem. I am sorry but I can't offer any solution. SoftMaker's development team is going to have to devise a conversion utility within the program.

[SuperTech]

Thanks for your post, but we are unable to reproduce this problem, and we are able to use both the formats. Please check the attached file imported from another software.

If you are still getting this problem, please send your file to forum[at]softmaker.com with link of this post.

[Woody44]

Thanks for your post, but we are unable to reproduce this problem, and we are able to use both the formats. Please check the attached file imported from another software.

If you are still getting this problem, please send your file to forum[at]softmaker.com with link of this post.

What software was your sample imported from?

I believe the problem Mikeism has encountered is caused by the fact that his source data are formatted as text at the source document, which makes it impossible for PlanMaker to recognize it as monetary values when imported. I just created a sample document consisting of three lines of monetary value, using Notepad. I then imported it into PlanMaker and attempted to add the three values using the SUM function. It failed, and returned a sum equal to zero.

In your sample file, you used a custom number format. I attempted to reproduce a usable function by creating a custom cell format using the same character string as in your sample file. After changing the format of all three of my cells to that format, the total remained displayed as zero not as -$285.00.

See attached:

PlanMaker_Sample.xlsx

(5.84 KiB) Downloaded 447 times

Notepad_Sample.txt

(26 Bytes) Downloaded 473 times