Remove spaces in string

[Former Member]

Hi.

I have some code that looks like this.

It returns numbers but the default setting when anything goes over 1000 is that it looks like this: 1 000.

I wonder if there is any way to remove the space?

Val ({CounterGoods.GOODS_PRICE}) + Val ({CounterGoods.GOODS_VAT})

[abhilash_kumar · Accepted solution]

The space might actually be the thousands separator.

Go to File > Options > Fields > Number > Number tab > Customize > Check what's in the 'Thousands Separator' box.

-Abhilash

[abhilash_kumar · Accepted solution]

You may even right-click the field > Select Format field > Number tab > Customize > Change the Thousands separator.

This will only affect the field whereas the 1st suggestion affects all number fields.

-Abhilash

[Former Member · Accepted solution]

I tried changing the thousands separator but that doesn't seem to do the trick. The thing is that the space is there from the database and I cant change it there. Also, I need to have the val function not to have other errors.

[abhilash_kumar · Accepted solution]

The Val() function will never output a string character; not even a space. It extracts numbers from the string field.

-Abhilash

[abhilash_kumar · Accepted solution]

In this formula : Val ({CounterGoods.GOODS_PRICE}) + Val ({CounterGoods.GOODS_VAT})

which field contains the space as part of the string?

-Abhilash

[Former Member · Accepted solution]

If the CounterGoods.GOODS_PRICE is more than 1000 it is presented as 1 000 and then the val only returns 1. I need to get rid of the empty space for my formula to work. This is at a recieptreport and I take price+vat*quantity. and it works as long as the price is below 1000.

[abhilash_kumar · Accepted solution]

If you remember one of your posts a few days ago, I stopped short of suggesting Not to use the Val() function as you said the issue was resolved.

The Val() function stops reading numbers the moment it finds a string in the field.

You should be looping through the string to extract the numbers for calculation. Something like this as previously suggested:

local stringvar temp := {GOODS_Price};

local stringvar Price;

local numbervar i;

for i := 1 to len(temp) do

(

    if temp[i] IN ["0" to "9"] then

        price := price + temp[i];

);

ToNumber(price) //you can use this as part of your calculations

-Abhilash

[DellSC · Accepted solution]

Or you might try something like this:

Val (Replace({CounterGoods.GOODS_PRICE}, " ", "")) + Val (Replace({CounterGoods.GOODS_VAT}, " ", ""))

-Dell

[Former Member · Accepted solution]

Hi.

The replacefunction is not working. Have tried that before  and the result is the same.

Abhilash, when I use your function the result is:

1 000,00 + 250,00 = 100 250,00

should be:

1 000,00 + 250,00 = 1 250,00

I cant just delete the decimals because sometimes we have amounts that needs to be there.

[abhilash_kumar · Accepted solution]

That happens because CR doesn't treat "," as the decimal by default. Modify the code to:

local stringvar temp := {GOODS_Price};

local stringvar Price;

local numbervar i;

for i := 1 to len(temp) do

(

    if temp[i] IN ["0" to "9"] OR temp[i] IN [",","."] then

        price := price + temp[i];

);

ToNumber(Replace(Replace(price,".",","),",","."));

-Abhilash

[Former Member · Accepted solution]

Thanks alot. That code did the trick.

[abhilash_kumar · Accepted solution]

Glad it works.

Don't forget to close the thread.

-Abhilash

[anubhab]

Hi,

You can try this.

Store the final output in a variable (eg. v_output).

Then, CONDENSE v_output NO-GAPS. This will remove all spaces within the value.

Hopefully this will give you desired output.

Regards,

Anubhab Chirui

[abhilash_kumar]

Thanks Anubhab, that will not work with Crystal Reports unfortunately.

-Abhilash