I need to apply the =proper function to a list of job titles but also apply exceptions to certain words like ABM, CEO, etc. What should I do?

=ARRAYFORMULA(IF(A1:A<>"", TEXTJOIN(" ", TRUE, IF( REGEXMATCH(TEXT(SPLIT(A1:A, " ")), "(?i)^(JP|AASM|CEO|NASA|J.P.|FBI|US|ABM|AAST|AE)$"), UPPER(SPLIT(A1:A, " ")), PROPER(SPLIT(A1:A, " ")) ))), ""))

This was the code I tried using, but Sheets gave me this error: "It looks like your formula is missing one or more open parentheses. If you don't want to enter a formula, begin your text with an apostrophe (')." I removed the parentheses, or the ones that were highlighted red, to ->

=ARRAYFORMULA(IF(A1:A<>"", TEXTJOIN(" ", TRUE, IF( REGEXMATCH(TEXT(SPLIT(A1:A, " ")), "(?i)^(JP|AASM|CEO|NASA|J.P.|FBI|US|ABM|AAST|AE)$"), UPPER(SPLIT(A1:A, " ")), PROPER(SPLIT(A1:A, " ")) ))), "")

And google Sheets gave me this error: " #N/A: Wrong number of arguments to ARRAYFORMULA. Expected 1 argument, but got 2 arguments."

I need to apply the =proper function to a list of job titles but also apply exceptions to certain words like ABM, CEO, etc. What should I do?

=ARRAYFORMULA(IF(A1:A<>"", TEXTJOIN(" ", TRUE, IF( REGEXMATCH(TEXT(SPLIT(A1:A, " ")), "(?i)^(JP|AASM|CEO|NASA|J.P.|FBI|US|ABM|AAST|AE)$"), UPPER(SPLIT(A1:A, " ")), PROPER(SPLIT(A1:A, " ")) ))), ""))

This was the code I tried using, but Sheets gave me this error: "It looks like your formula is missing one or more open parentheses. If you don't want to enter a formula, begin your text with an apostrophe (')." I removed the parentheses, or the ones that were highlighted red, to ->

=ARRAYFORMULA(IF(A1:A<>"", TEXTJOIN(" ", TRUE, IF( REGEXMATCH(TEXT(SPLIT(A1:A, " ")), "(?i)^(JP|AASM|CEO|NASA|J.P.|FBI|US|ABM|AAST|AE)$"), UPPER(SPLIT(A1:A, " ")), PROPER(SPLIT(A1:A, " ")) ))), "")

And google Sheets gave me this error: " #N/A: Wrong number of arguments to ARRAYFORMULA. Expected 1 argument, but got 2 arguments."

• google-sheets

2 Answers 2

Try this out:

=ARRAYFORMULA(
   MAP(A2:A, LAMBDA(a,
     IF(a = "", , LET(
      s, SPLIT(a, " "),
      ex, "CEO|AAPM",
      JOIN(" ",
        IF(
          REGEXMATCH(s, "^(" & ex & ")$"),
          s,
          PROPER(s)
        )
      )
     ))
   ))
 ) 

Applying Proper Function on Selected words on a String

You can also try this another approach. Using Reduce to Iterate through the words.

Formula

=Byrow(A1:A, LAMBDA(r, IF(ISBLANK(r),"",REDUCE(,SPLIT(r, " "), LAMBDA(a,c, IFNA(IF(MATCH(c, SPLIT("JP|AASM|CEO|NASA|J.P.|FBI|US|ABM|AAST|AE|AAPM","|"),0)>0,JOIN(" ",a,c),), JOIN(" ",a,PROPER(c))))))))

Result

References:

Reduce