CONCATENATE but exclude blank cells

[Yolanda_Enoch]

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},”"))

Thanks!

[W_Vann_Hall]

Easiest way:

{First Name}&
IF(
    {Middle Name},
    ' '&{Middle Name}
    )&
' '&
{Last Name}

[Yolanda_Enoch]

This formula worked, thank you!

[Elias_Gomez_Sai]

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

[Justin_Barrett]

Could you please share the formula that you say is failing? We can’t help you unless you give us something to work with. :winking_face:

[Justin_Barrett]

@Carlos_Hurtado Checking once more to ask if you could share the formula that you say is failing. Unfortunately, @W_Vann_Hall hasn’t been active for a while, but there are others who could also help.