Best way to eliminate extra spaces in a formula

[JimO]

This is my formula to display the Full Name. My issue is that if there is only say a first name and a last name the full name has an extra space in it (Jim space space Jones), I am looking for a way to correct this without using an IIf clause for each argument and building a long involved formula. I am not sure if this is possible, I thought using the Trim would be sufficient but it is not.

FullName: Trim([Prefix] & " " & [FirstName] & " " & [MiddleName] & " " & [LastName] & " " & [Suffix])

Jim O

[andy49]

Since trim only removes leading and end spaces why not use


Replace (text, " "," ")


Sent from my iPhone using Tapatalk

[RuralGuy]

You mighty try just using a "+" instead of a "&" in the right place. The "+" will propagate the null value so no space will be added if the [MiddleName] is null.

FullName: Trim([Prefix] & " " & [FirstName] & " " & [MiddleName] + " " & [LastName] & " " & [Suffix])

[andy49]

Now I never knew that. Thanks rural!![emoji3]


Sent from my iPhone using Tapatalk

[RuralGuy]

Glad to provide the information. It's a slick trick.

[JimO]

Would I use that in addition to Trim or in place of Trim?

Jim O

[andy49]

Don't think trim will make any difference.


Sent from my iPhone using Tapatalk

[RuralGuy]

Trim will take care of any leading and trailing spaces so it is in addition to...

[JimO]

Thanks to all for the time and expertize.

Jim O

[RuralGuy]

Just for the record, you could use the same scheme for the [Prefix] and [Suffix] fields and not use Trim:
FullName: [Prefix] + " " & [FirstName] & " " & [MiddleName] + " " & [LastName] & " " + [Suffix]