Calculation for text spacing to also accomodate when null

[4Justme2]

I had a field to concatonate a person's FullName:  NameFirst&" "&MiddleInitial&" "&LastName&" "&Suffix

 

But I noticed, if the middle initial field was blank I'd still get the additional space between the NameFirst and MiddleName.  So I tried modifying the FullName field as follows in an effort to get rid of the additional space in those cases where the MiddleInitial was null (but this didn't work either):

 

 

Trim(NameFirst)&" "&Trim(MiddleInitial)&" "&Trim(NameLast)&" "&(Suffix)

 

Example above produces: Bill H Muldoon Sr

 

However, if the MiddleInitial field is null the above calculation produces an extra space between the NameFirst and the MiddleInitial.

 

Bill  Muldoon Sr… when what I want to produce is

Bill Muldoon Sr

 

How can I modify this calculation so if the MiddleInitial field is null there is no space between NameFirst and MiddleInitial?

 

Any suggestions would be greatly appreciated. 

[LaRetta]

Try either:

Substitute ( List ( NameFirst ; NameMiddle ; NameLast ; Suffix ) ; CR ; " " )

... where CR is pilcrow ( I can't make it on iPad.)

Or

Trim ( NameFirst & " " & NameMiddle & " " & NameLast & " " & Suffix )

Edited November 23, 2012 by LaRetta

[4Justme2]

Many thanks, LaRetta... mp

[comment]

Have a look at the TrimAll() function.

[LaRetta]

Oh geez, I original posted TrimAll() and then changed it.  That is what I get for answering within 30 seconds of waking up ... no brain yet.  Thanks for the catch Michael. :^)

 

So it would be, as in the first email you received, Marsha:

 

TrimAll ( NameFirst & " " & NameMiddle & " " & NameLast & " " & Suffix ; 0 ; 0 )