Rule to find last comma and add space and word and after it.

[maryj]

I need help creating a FP rule that will go from the right and at the first comma add a space and the word "and", but not change any of the other commas if there are any. Is it possible to do this and if it is, can you tell how the rule would be?

 

Thanks!

[Andre Globensky]

Try this.

 

Change the string between quote to your field (var lcString = Field("xyz"))

 

var lcString = "Looking for, the last comma, of string"

var lnPosition = lcString.lastIndexOf(",")

var part1 = Trim(lcString.substring(lnPosition ,-1));

var part2 = Trim(lcString.substring(lnPosition + 1 ));

return part1 + ' and ' + part2

Edited August 29, 2011 by Andre Globensky
translation of comma in french error, should of been , instead of . (period)

[Dan Korn]

return Field("YourFieldName").replace(/,([^,]*)$/, ", and$1");

[maryj]

Thanks Dan, that worked great, but now I need it to do one more thing. I need that same rule to add a space after the other commas in the field. This is what is in the field:

 

Home purchase/refinance,Business checking account,Business/commercial loans

 

I need the rule to make it say:

 

Home purchase/refinance, Business checking account, and Business/commercial loans

 

Is this possible with one rule?

 

Thanks,

 

Mary

[Dan Korn]

I need that same rule to add a space after the other commas in the field. This is what is in the field:

 

Home purchase/refinance,Business checking account,Business/commercial loans

 

I need the rule to make it say:

 

Home purchase/refinance, Business checking account, and Business/commercial loans

 

Is this possible with one rule?

return Field("YourFieldName").replace(/,([^,]*)$/, ",and $1").replace(/,/g, ", ");