CONCATENATE but exclude blank cells


I have one field that I would like to generate from three other ones: First Name, Middle Name, and Last Name.

My current formula includes a space after each cell so that it reads Jane Doe Smith versus JaneDoeSmith.

The problem I have is if the middle name field is blank (I always have the first and last name of the person), I get a double space between the first and last name.

I’m certain there’s some sort of If/Then formula I should be using, and I’ve looked at online resources, but I’m having a hard time getting it to work (I get the invalid formula error message).

Tried this:

CONCATENATE({First Name}&IF({Middle Name}<>""," “&{Middle Name},)&IF({Last Name}<>”"," “&{Last Name},”"))



Easiest way:

{First Name}&
    {Middle Name},
    ' '&{Middle Name}
' '&
{Last Name}


This formula worked, thank you!


Of course, because the space for Middle Name is inside the IF for that field.