0

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

Cell-Contents

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! :-)

1 Answer 1

0

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().

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.