Remove Spaces from Basic Formula If Field Is Null

[kpcollier]

I have this basic table formula:

IsNull([@field:Section_String], '') + ' ' + 
IsNull([@field:Manuf_String], '') + ' ' + 
IsNull([@field:Category_String], '') + ' ' + 
IsNull([@field:SubCat_String], '') + ' ' + 
IsNull([@field:Description], '') + ' - ' + 
IsNull([@field:Finish], '')

However, if one of the fields is null, the space is still there. This leaves a double space in the middle of the value.

Anyone have any ideas how to get rid of the space following a field if it is null?

[kpcollier]

I removed the IsNull from each field and wrapped the whole formula in a couple of replace() method.

replace(replace(replace(([@field:Section_String] + ' ' + [@field:Manuf_String] + ' ' + [@field:Category_String] + ' ' + [@field:SubCat_String] + ' ' + [@field:Description] + ' - ' + [@field:Finish]),' ','<>'),'><',''),'<>',' ')

Since the value would just be blank if the field is empty anyways, IsNull isn't needed. Then, the replace method will make any double/triple spaces into a single space.

[CoopperBackpack]

Hello @kpcollier,

I believe that there is one more option to delete extra spaces, commas, etc. by using the combination of NULLIF() and COALESCE() functions.