Jump to content
Salesforce and other SMB Solutions are coming soon. ×



Recommended Posts

I am having trouble with some concatenation logic.

I have 6 fields dealing with names, company titles and company names.

They are

Personal title

First name

middle initial

Last Name

Company position

Company name.

Any or most of these fields may be empty and the concatenation includes spaces and, in two cases, a comma.


Personal Title First name Middle Initial last name, Company position, Company Name.

To do a complete concatenation is easy but it leaves spaces and commas when there is no data. How do I omit the fields without data and the spaces?

Link to comment
Share on other sites


Set each part of the concatenation with eith an IF or a CASE statement. Since I only have experience with FM7 I'm not sure what would be appropriate for other cases. Basically you want a calc that says:


Concat =

IF(not IsEmpty(Field1) ; Field1 & ", " ; "") &

IF(not IsEmpty(Field2) ; Field2 & " " ; "")




The syntax I used for the above calc is not the best but the idea should be clear.

Link to comment
Share on other sites

You'll want to use the Substitute function to get rid of the commas and Trim to get rid of the trailing spaces. The whole calc would be like this, allowing for source fields to have spaces in them but not the data you want:



IF(Trim(Personal title)≠""; Trim(Personal title)&" ";"")&

IF(Trim(First name)≠""; Trim(First name)&" ";"")&

IF(Trim(middle initial)≠""; Trim(middle initial)&" ";"")&

IF(Trim(last name)≠""; Trim(last name)&" ";"")&

IF(Trim(Company position)≠""; Trim(Company position)&" ";"")&

IF(Trim(Company name)≠""; Trim(Company name)&" ";"")


Link to comment
Share on other sites

I think I have set you off on the wrong road here.

It is not the data itself which needs trimming, it is OK, but when I concatenate the data (which will rarely have data in all the fields) I don't know how to tell the calculation to ignore both the data and space in the calculation.


a straight concatenation would be

Personal title&" "&first name&" "&middle initial&" "&last name&", "&company title&" , "&company name.

If there is no data in middle initial, I want the calculation to ignore both "middle initial" and "&" "&"

Link to comment
Share on other sites

This one is a bit cleaner.




Personal Title & " " & First Name & " " & Middle Initial & " " & Last Name & Case( not IsEmpty(Company Position); ", " ) & Company Position & Case( not IsEmpty(Company Name); ", " ) & Company Name;

0; 0 );

" ,"; "," )

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

Terms of Use