we accepted a rush job that has about 600 records but the problem is that in the excel file...the variable for dollars is, for example: 1214.44 but the client needs the formatting to be like:
1 214,44 which I have never seen before but this is what they want.

I'd hate to have to edit every one separately in excel but I can't think of any other way.

Any help appreciated

Hi,

You could use the following expression to display the amount abovementionned, assuming the Excel field name containing the amount is 'Test':

The expression instructs PSM to:

1. Display the leftmost part of the amount, minus the last 6 characters
2. Display a space as the thousand separator only if the amount string has more than 6 characters
3. Display the 6 rightmost characters of the amount, also replacing the dot by the comma in the process.

Note: This expression doesn't handle all cases: it doesn't add a millon separator, and doesn't take negative amounts into account.

Hope this helps!

NB. There might still be solutions to try, like saving your Excel file with different export settings, such as using a comma instead of a dot as the decimal separator. To learn more on this topic you should consult the Excel Help.


Benoit

thanks for this.

I'm thinking that also, it may be better to tackle the problem in the excel file itself. I like this program but in my modest use of it, I am finding it beneficial to try and solve the problem first in Excel.

Also, we are only dealing with amounts of money within 1,000 to 9,000 dollars. What I mean is the amount is, for example, minimum: 1,215.22 to maximum: 8,995.29

This won't help you right now, but:

You can specify formatting rules in PSM without defining an expression. Select your variable in the Variables pane, then look at the Properties pane. Change the Category property to Currency, then change the Locale property to French. This should allow you to choose French-specific values (i.e. a space) for the Digit Grouping Symbol property.

Unfortunately it won't work in the current version of PSM. The values for Digit Grouping Symbol are always limited to an empty string, a comma, and a period.

I'll log this as a bug, I think it should be changed.

This code seems to work except that I'm getting:

eg. 1, 200,50 which is all good except for the comma after the 1. It should be 1 200,50

In PSM the result has the comma after the 1 even though it's not there in the excel file.

I played a bit with this...

Interestingly, it was working when I first started a new document. I didn't even need to use any expressions, it was formatting the 1214.44 number properly, with the commas and the space. In the properties of the variable, the "Digit Grouping Symbol" was set to a space by default. However, I changed it, and when I tried switching back to the space, it instead gave me an empty character, and I couldn't put back the space again.

I wonder if the space's default selection has something to do with the system's regional settings? (mine is a french environment) Pspdfppd, maybe you can try creating a new variable to display your field, and see if the space is selected as the grouping symbol? If not, try maybe closing PSM, set your system's regional settings to a french environment temporarely(it shouldn't turn all menus to french, don't worry), and then recreate the variable. If it adds the space properly, save the document, then revert back to english environment. Do not change the grouping symbol again for that variable. I do think that this should be fixed for the next release, and that we should be able to put whatever we want as the grouping symbol.


With that said, Sander's code also seems to work for me for the first number. For the second, however, it doesn't seem to add the space, probably because in 1200.50, it doesn't display the 0 after the 5. In your case, does 1200.50 shows up as this, or does it shows up as 1200.5 or something else?

Hope that helps.

Regards,
Rapha

Hi, well the job has been printed and folded and will mail tommorrow morning and I couldn't see anything wrong with any of the records using Sanders code. I had to change the cells content to "numbers" with 2 decimal points and that seemed to eliminate the rounding off. I just put the $ symbol inline with the variable. Still trying to work back and understand how that expression worked. I'm very happy that this site bailed us out but I am the type that really wants to know HOW it works

sorry about last comment. I mean this worked with the Benoit Potvin code.