Jump to content

Separating items in a calculating field


This topic is 7831 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7831 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.