In Google Sheets, how to join 3 cells, in 3 different ways, depending on the contents of 1 of those cells

Question

I started this on 1/26 and learned I had curly quotes that were breaking the formula. I worked through that, with help here, but now I'm at the last piece, allowing 2 owners with different last names to print out like I want them to.

So far my formula works “OK”, but it’s not perfect: =TEXTJOIN(" ", TRUE,(IMPORTRANGE("","Directory!D5")),IF((IMPORTRANGE("","Directory!E5"))<>"","&",""),(IMPORTRANGE("","Directory!E5")),(IMPORTRANGE("","Directory!C5")))

What exists on Worksheet A: Column C Column D Column E LAST NAME (1st OWNER) 1st NAME (1st OWNER) 2nd OWNER {1st, (LAST)} Ex 1 Smith Marshall Julie --- Ex 2 Jones Chris Samantha Ex 3 Anderson Patrick Jane Doe

How I want it to pull onto Worksheet B: Column C OWNER(S) WHAT “SHOULD” PRINT IN THE CELL Ex 1 Julie Smith Marshall NO “&” symbol Ex 2 Chris & Samantha Jones “&” symbol between the 2 first names Ex 3 Patrick Anderson & Jane Doe “&” symbol between the 2 full names

BUT, what I’m getting on Worksheet B, using the code above: Column C OWNER(S) WHAT ”IS” PRINTING IN THE CELL Ex 1 Julie Smith Marshall NO “&” symbol Ex 2 Chris & Samantha Jones “&” symbol between the 2 first names Ex 3 Patrick & Jane Doe Anderson 2nd owner name in the middle of 1st owner F/L name

What I think I need…somehow:

1. currently… A. if there is NO value in Column E, this is working fine (ex. 1) B. if there IS a value in Column E, and it’s ONLY ONE WORD (a “first” name), this is working fine (ex. 2) C. BUT, this is NOT working if the Column E value is TWO WORDS (a “first” and a “last” name) D. NOTE: Column E is entered in the cell as: FirstName LastName (ex. 3)
2. so to brainstorm… A. NOTE: data on Worksheet A is VERY “clean”; NO extraneous “spaces”, etc.; I did the “trim whitespace” action, and there was none B. right now the IMPORTRANGE “read” is in this order: Column D, if Column E is blank skip Column E else print the "&" then Column E then Column C C. the only difference between when it works/not is the # words/names in Column E D. so if Column E is NOT blank, then it needs to check for a “space” in that text... i. if there is NO space, do what it’s doing now, a. print the "&" then Column E b. so the print order is: D “&” E C ii. if there IS a space, then skip and a. go to next IMPORTRANGE for Column C b. then print the "&" then do IMPORTRANGE for Column E then print Column E c. so the print order ends up: D C “&” E E. I’ve ruled out, for various reasons: i. QUERY ii. LIST iii. FILTER iv. ARRAYFORMULA v. CONCATENATE F. I keep thinking I should be able to “nest” these functions within the IF function G. I have played with TEXTJOIN and the IF function in conjunction with: i. SEARCH ii. MATCH iii. REGEXMATCH

But alas, I’ve struck out on every attempt…ARGH! I’m not having any luck with getting a successful function that “works”! I just keep getting “formula parse error”. I’ve verified all of my quotation marks are straight quotes. I’ve verified equivalent open/close parentheses. I’ve verified my . I’m just frustrated and lost. Can anyone point me in the correct direction? Much appreciation for ANY help! :-)

Answer 1

In the 'Directory' sheet, insert this formula in row 2 of a free column:

=arrayformula( 
  ifs( 
    isblank(E2:E), 
      D2:D & " " & C2:C, 
    regexmatch(E2:E, " "), 
      D2:D & " " & C2:C & " & " & E2:E, 
    len(E2:E), 
      D2:D & " & " & E2:E & " " & C2:C, 
    true, 
      C2:C 
  ) 
)

You can then copy the results from the new column to the target tab with an { array expression }, or to another spreadsheet file with importrange().