Replace comma with a space within a field

[mart10]

I am using CR XI

I have the following formula:

If isnull({GL_ENTRY.comments}) then {codeset.item_name} else {GL_ENTRY.comments}

However I have just found out that {GL_ENTRY.comments} sometimes contains a , and this can be anywhere as users use this filed to put in comments. As we are extracting to our accounting system I have been given the rule that each field MUST be seprated by a ,

Hence I want to use above formual but test for any , (may be multiple and replace with a space

 

[Madawc]

Do you want one space for one or more commas, not present with anything else. Then adjust your formula to

If isnull({GL_ENTRY.comments}) 
then {codeset.item_name} 
[b]else if Left({GL_ENTRY.comments}, 1) = ","
then " "[/b]
else {GL_ENTRY.comments}

There is also Rep, but that would give you one space per comma.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I don't follow the logic of why you want to replace the comma with a space, but you can use:

replace({table.comments},","," ")

-LB

 

[CR85user]

I think I follow the logic.

The OP is exporting the data to a seperate accounting package in csv format and does not wish to confuse the import by multiple columns where ',' appears as part of the text. Or at least I think that is what he is saying?

As LBass stated the operator for replacing such text within a field is replace()

If isnull({GL_ENTRY.comments}) then {codeset.item_name} else replace({table.comments},","," ")

'J

 

[mart10]

Yes thanks I will try this

Yes CR85User you are correct. I have a large number of fields which are concatenated into one text field for ADI import to Oracle Accounts. Journal details - sometimes users put a , in their text. This will be our Crystal solution and hopefully we may have an Oracle import solution ie use a ~ seperater instead of a ,