Combining 3 columns into a new field w-out separators showing up if some fields are empty

[cgoad61]

Hello All,

I am trying to combine/group/concatenate 3 columns of data into 1 new field.  I was able to do this easily with the formula (ex: [CCCAP Filing Number]+"; "+[CER FDICIA Filing Number]+"; "+[CER USTier1C Filing Number]) but then I end up with extra semi colons when any of the original columns are empty/blank.  I also tried an If IsNull... formula which I've used before successfully in similar situations (with less fields) but it doesn't work.  This would've been the first of 3 formulas (at least) to capture each scenario...

If IsNull [CCCAP Filing Number] and [CER FDICIA Filing Number] and [CER USTier1C Filing Number]
then [CCCAP Filing Number]+"; "+[CER FDICIA Filing Number]+"; "+[CER USTier1C Filing Number]
else ""
ENDIF

I would appreciate any help you can lend please.  I've attached a file with the original data tab as well as a tab reflecting the final result I'm hoping for but here is also an image of the 2 new fields I'm hoping to get (I only listed Filing number in my examples above but I'll be doing the same for the Sub Schedule fields as well).  Thank you in advance for anything you can suggest...

[usmanbashir · Accepted solution]

@cgoad61 - I transposed the data then concatenated using summarize. Hope this helps!

 

[cgoad61]

Thank You usmanbashir!

I apologize for the delay in my reply but this was very helpful!  It seems to be working great!  Have a Wonderful Day!