-2

I'm new to this, but I finally got something working that does what I need in Google Sheets, and I got it all into one cell. But it won't work with the ARRAYFUNCTION which is necessary for the sheet I'm making; where new Rows could be added in the list at any time.


To explain what the function needs to do:

First it needs to look at 3 data fields input by a human. All of these are in 3 separate cells on the same row.

  1. Column A, Name
  2. Column B, Number
  3. Column C, Category (this is a drop-down)

Then it takes the input of #1 and uses TRIM to and SUBSTITUTE (spaces " " or dashes "-") to remove any human error and place underscores between words.

Next is checks the category and if it matches 1 type it formats the output in style 1, else the format becomes style 2. When it does this TEXTJOIN is used to place the result of the last part together with the rest of the formatting.

Finally it displays the output in all capital letters.


The Code in Question:

IF( ISTEXT( INDIRECT( "RC[-3]", FALSE)), UPPER(  TEXTJOIN( , , IF( INDIRECT( "RC[-1]", FALSE)="type1", TEXTJOIN( , , "type1_ref(", IF( N( INDIRECT( "RC[-2]", FALSE))<9, " "&N( INDIRECT( "RC[-2]", FALSE)), N( INDIRECT( "RC[-2]", FALSE))), ", ref_" ), "ref_"), SUBSTITUTE( SUBSTITUTE( TRIM( INDIRECT( "RC[-3]", FALSE)), " ",  "_"), "-", "_"), IF( INDIRECT( "RC[-1]", FALSE)="type1", "),", ","))), "—")

This is the completely nested version, but I'll break it down by sections:

IF( ISTEXT( INDIRECT( "RC[-3]", FALSE)), ... , "—”)

This IF determines if there is text in Column A. If there's not then the output is just a dash. INDIRECT is used to always target a cell exactly 3 cells of the left.

UPPER(  TEXTJOIN( , , IF(( INDIRECT( "RC[-1]", FALSE)="type1", ... ", ref_", "ref_"), SUBSTITUTE(), IF((INDIRECT( "RC[-1]", FALSE)="type1", "),", ","))

When there is text in Column A then it uses the TEXTJOIN function to generate the formated output on the left and right side of the output. All of which is made uppercase by UPPER at the end.

The IF functions both check if the drop-down Category in Column C is type1 or not, synchronizing the left and right side of the TEXTJOIN output. And the SUBSTITUTE in the middle will produce the words from Column A.

TEXTJOIN( , , "type1_ref(", IF( N( INDIRECT( "RC[-2]", FALSE))<9, " "&N( INDIRECT( "RC[-2]", FALSE)), N( INDIRECT( "RC[-2]", FALSE))),

In the "..." above goes this TEXTJOIN which primarily has the function of formatting the number from Column B based on the number of digits it has. If it's only 1 (determined if it's value is 9 or less) then a space is used in the 10's place. Then it outputs "type1_ref(##, ref_" where the ## are the numbers. (The numbers should only ever be between 0-99)

The N() function is used because in testing the INDIRECT function was copying everything over, including formatting or even font choice. N() provides only the numeric contents.

SUBSTITUTE( SUBSTITUTE( TRIM( INDIRECT( "RC[-3]", FALSE)), " ",  "_"), "-", "_"),

In the empty parenthesis above goes this. Which simply targets the contents of Column A and removes leading or lagging spaces and double spaces. Once only the expected amount of spaces remain, Substitute replaces any dashes or spaces with underscores.


All combined these nested functions, when the data is entered correctly, will output either:

  • TYPE1_REF(##, REF_EXAMPLE_TEXT),
  • REF_EXAMPLE_TEXT,

This is exactly the right format I want for the final output.

But it won't work with ARRAYFUNCTION!

What can I do? Do I need to change something? Is there something I'm missing or did wrong?

1

2 Answers 2

1

The formula in the question seems unnecessarily convoluted. The same can be done with plain vanilla references that work in an array formula, like this:

=arrayformula( 
  if(isblank(A2:A), "—", 
  upper( 
    C2:C & "_REF(" & 
    right(" " & B2:B, 2) & ", REF_" & 
    regexreplace(trim(A2:A), "[- ]", "_") 
    & ")," 
  ) 
))
3
  • This is very informative, but it fails to accomplish half of what mine did. Thank you for the answer regardless. | However, firstly it just rips the category out of column C which is not what it should be doing as that column is for human-picked categories and will not match the code's syntax. Secondly this only outputs the first format of my first example and never the second. They may look very similar, but the placements of every () and , are very important. | That aside, I love the RIGHT function! I had no idea it was so flexible! I'll definitely need to remember that one.
    – ZweiHydra
    Jan 4 at 22:12
  • 1
    Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Jan 4 at 22:18
  • Er, no? The question clearly lays out that it needs to produce 2 different formats, & I broke down when and what the criteria was. It's also not helpful of you to make assumptions that my boolean on the category was completely superfluous and remove it. Regardless, you were very helpful and I'm thankful for your reply. With it I figured the rest out and posted my own solution. Thank you.
    – ZweiHydra
    Jan 5 at 1:22
0

Thanks to @doubleunary I was able to significantly improve the code. My main issue was believing I couldn't list a range when trying to reference only 1 cell.

Using their formula as a template, I corrected it to fit the criteria of the question:

=ARRAYFORMULA(
  IF(ISBLANK(A2:A), "—", 
  UPPER( 
    IF( C2:C="type1", 
      "category_1_ref(" & RIGHT("  " & B2:B,2)
      & ", ref_" & REGEXREPLACE(TRIM(A2:A), 
      "[- ]", "_") & ")," ,
      "ref_" & REGEXREPLACE(TRIM(A2:A), 
      "[- ]", "_") & "," 
    )
  )
  )
)

This now properly reads the columns' entrees to output one of the two formatted answers,

  • CATEGORY_1_REF(##, REF_EXAMPLE_TEXT),
  • REF_EXAMPLE_TEXT,

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.