Separating items in a calculating field

[btmeacham]

Hello All,

I've looked around for a hint on this item but haven't been able to find any

info. I'm defining a calculating field based on the data from four other

fields. In all cases, the first of these four fields has data, but the other

three fields do not necessarily have data for any given record.

I want to simply list the items gathered from the four fields in this new

field, separated by commas. I can do this by calculating Field 1 & ", " &

Field 2 & ", " etc..., but I run into problems when there is only data in

the first field or two.

I want the combined field to use commas to separate the data when necessary,

but I often run into calculated records that will look like this:

Data,,, - with all of those unnecessary commas showing up to separate items

that do not exist.

Anyone have any ideas on how to get the calculation to fail more gracefully

when some of the fields are empty? Thanks very much for your help,

Brian

[BobWeaver]

You can delete the excess commas with this function:

Substitute(Substitute(

MiddleWords(Field1,&","&Field2,&","&Field3,&","&Field4,1,64000),

",,", ","),",,", ",")

The MiddleWords function deletes any commas at the beginning or end of the text, and the Substitute functions replace duplicate commas with single commas.

[btmeacham]

Brilliant! Thanks so much, worked like a charm.

Brian