Help

CONCATENATE but exclude blank cells

3184 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Yolanda_Enoch
4 - Data Explorer
4 - Data Explorer

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!

5 Replies 5

Easiest way:

{First Name}&
IF(
    {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.

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:

@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.