admin管理员组

文章数量:1415139

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."

Job Title header 2
CEO DATA Management CEO Data Management
STUART LI Stuart Li
AAPM AAPM

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."

Job Title header 2
CEO DATA Management CEO Data Management
STUART LI Stuart Li
AAPM AAPM
  • google-sheets
Share Improve this question asked Feb 10 at 23:54 William WillWilliam Will 111 silver badge1 bronze badge
Add a comment  | 

2 Answers 2

Reset to default 1

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

Sample Result
CEO DATA Management CEO Data Management
STUART LI Stuart Li
AAPM AAPM

References:

Reduce

本文标签：

版权声明：本文标题：Is there a combination of functions on google sheets that can proper a list of words but also apply exceptions for abbreviations 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741685173a2392403.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。