Don't leave space if the field is empty

[chadski021]

Hello,

 

Can Someone help me i have a merge code lets say i have 3 fields firstname, middlename and last name and i have a substitute field to put this 3 field

 

Substitute ( Self ;

[ "<<firstname>>" ; table::firstname ] ;

[ "<<middlename>>" ; table::middlename ] ;

[ "<<lastname>>" ; table::lastname ] ; 

)

 

and i put this on this substitute field for example 

 

My name is <<firstname>> <<middlename>> <<lastname>>

 

My problem is if the <<middlename>> is empty it leaves a space is there a way to remove the space without actually removing the <<middlename>> on the center?

 

[comment]

You could either add the spaces conditionally, based on the result of IsEmpty ( middlename ) - or attack the problem globally, using the TrimAll() function.

[Lee Smith]

Simple answer would be FirstName & “ “ & MiddleName & “ “ & LastName assuming all fields are not empty?

 

Most of the time the data in the fields are not always that clean.

 

This would probably work for you

// Trim removes and leading and trailing spaces. Substitute removes the extra spaces if there is no middle name.

 

Trim(

    Substitute(

        Title & " " & First & " " & Middle & " " & Last;

        [ "  "; " " ];

        // Remove 2 spaces

[ "¶"; "" ]

        // Remove extra carriage returns

    )

)

[David Jondreau]

I like List() and Substitute().

 

Let( [

first.name = Substitute ( Self  ; "<<firstname>>" ; table::firstname  ) ;

middle.name = Substitute ( Self  ; "<<middlename>>" ; table::middlename  ) ;

last.name = Substitute ( Self  ; "<<lastname>>" ; table::lastname  ) ;

 

result = Substitute ( List ( first.name ; middle.name ; last.name ) ; "¶" ; " " )

];

result

)