excelexcel-formula

Name on Envelope


I have a spreadsheet with one to two names per row, broken into first and last names. I'm trying to concatenate the names, the result depending on whether the last name is the same or different, or even not present. My goal is to have a cell to use for the envelope name in a form that is professional.

fname1 lname1 fname2 lname2
Joe Smith Linda Smith
Danny Larson Shannon Carter
Bart Simpson blank blank

I am trying to figure out a formula that would result in

"Joe and Linda Smith" (concat fname1, " and ", fname2 and lname1 if lname1 and lname2 are the same)

"Danny Larson and Shannon Carter" (concat fname1, lname1, " and " fname2, lname2 if lname1 and lname2 are NOT the same)

"Bart Simpson" (concat fname1 and lname1 only if fname2 and lname2 are empty)

I've tried all kind of combos with no real breakthroughs, I think it's just outside my skill range.


Solution

  • I would have logic that sees if there's a pair, and if so combine accordingly. If not, confirm there is no name in the second, then print the first full name. Then, if all fails, it prints both names individually together.

    =IF(B2=D2, A2 &" and "& C2 &" "& D2 , IF(ISBLANK(C2),A2 &" "& B2, A2 &" "& B2 &" and "& C2 &" "& D2 ) )

    Image of spreadsheet showing output and inputs for results