How to convert first letter of each word to upper case after skipping mentioned words in Google Shee-CodePudding

I want to convert the first letter of all words in string except mentioned words. Direct "Proper" function will not work as it will capitalize all first letter string.

Example: "You can install it in a few clicks and it gives you a lot many options when it comes to changing case in Google Sheets."

I want to convert the above sentence to "You can Install it in a Few Clicks and it Gives you a Lot Many Options when it Comes to Changing Case in Google Sheets."

ignored words: it, and, in, can, you, a, to

Thanks for your help in advance. Vineet

CodePudding user response：

Use regexreplace(), like this:

=lambda(
  data, lowercaseWords, 
  byrow( 
    data, 
    lambda( 
      row, 
      mid( 
        reduce( 
          proper("_" & to_text(row)), lowercaseWords, 
          lambda( 
            acc, word, 
            regexreplace(acc, "(?i)\b" & word & "\b", word) 
          ) 
        ), 
        2, 9^9 
      ) 
    ) 
  ) 
)( 
  A2:A10, 
  { "it", "and", "in", "can", "you", "a", "to" } 
)

CodePudding user response：

Give a try on the following formula-

=LAMBDA(x,TEXTJOIN(" ",1,INDEX(IF(ISERR(SEARCH(x,"it,and,in,can,you,a,to")),PROPER(x),x))))(FLATTEN(SPLIT(A1," ")))

For dynamic input range, try-

=MAP(A1:INDEX(A1:A,COUNTA(A1:A)),LAMBDA(lm,LAMBDA(x,TEXTJOIN(" ",1,INDEX(IF(ISERR(SEARCH(x,"it,and,in,can,you,a,to")),PROPER(x),x))))(lm)))

CodePudding user response：

You can use a list of words you don't want to capitalize in a certain range you stablish with the next formula. Please change A1:A with your column or range and B1 with the cell of your expression:

=REDUCE(PROPER(B1),FILTER(A1:A,A1:A<>""),
LAMBDA(phrase,words,
REGEXREPLACE(phrase,""(?i)\b"&words&"\b",words)))

If you want you can use it as an arrayformula too for a whole column of phrases B1:B:

=ArrayFormula(REDUCE(PROPER(B1:B),FILTER(A1:A,A1:A<>""),
LAMBDA(phrase,words,
REGEXREPLACE(phrase,"(?i)\b"&words&"\b",words))))