CODEWITHSUNDEEP
if-statementgoogle-sheetsspreadsheet
Debora S.
Debora S.

Reputation: 115

Having a problem with IF function argument in Spreadsheet

I'm trying to make this description generator, and I can't seem to make the first part work for one of the IF arguments as it does with the rest. It only checks the logical expression but doesn't bring the rest of the text body in the cell joined with & as it does in the case of the other IF arguments I have there linked one after the other. This example should make more sense.

Upvotes: 0

Views: 51

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

The first If statement encloses all the rest of the formula so if the regex matches "ck" the condition is satisfied, you get "click >" but nothing else happens. I think you can just move the final bracket so it is just after "select " like this:

=IF(REGEXMATCH(B4, "ck"),"click >",IF(REGEXMATCH(B4, "scd"),"scroll down > ",IF(REGEXMATCH(B4, "!"),"","select "))) & ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(TEXTJOIN(CHAR(10), 1, IF(REGEXMATCH(""&
 SPLIT(B4, CHAR(10)), "^>.*"), 
 SPLIT(B4, CHAR(10)), TRANSPOSE(MMULT(TRANSPOSE(TRANSPOSE((SEQUENCE(1, COLUMNS(
 SPLIT(B4, CHAR(10))))<=SEQUENCE(COLUMNS(
 SPLIT(B4, CHAR(10))), 1, 0))*NOT(REGEXMATCH(
 SPLIT(B4, CHAR(10)), "^>.+")))), TRANSPOSE(SIGN(NOT(REGEXMATCH(
 SPLIT(B4, CHAR(10)), "^>.+"))))))&". "&
 SPLIT(B4, CHAR(10)))), "^0. ", ),"scd",""),"ck",""),"!",""))

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

try:

=INDEX(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(IFERROR(
 SPLIT(B1:B, CHAR(10)))="",,REGEXREPLACE({"", SEQUENCE(1, 100)}&". "&IFNA(VLOOKUP(
 TRIM(SPLIT(B1:B, CHAR(10))), 
 {"ck",  "click >";
  "box", "select box"; 
  "scd", "scroll down >"}, 2, 0), 
 SPLIT(B1:B, CHAR(10))), " ", CHAR(13)))),,9^9))), 
 " ", CHAR(10)), CHAR(13), " "), "^\. !?", ))

enter image description here

demo sheet

Upvotes: 2

Related Questions