How can I use the capturing group in a function?
How can I use the capturing group in a function?
=REGEXREPLACE("string", "(ng)", UPPER("$1"))
=REGEXREPLACE("string", "(ng)", UPPER("$1"))
The above returns "string", but should return "striNG".
I suspect that this is because UPPER is capitalizing the string "$1" rather than the string with the captured group substituted into it.
UPPER
How can I make REGEXREPLACE output the expected result?
REGEXREPLACE
3 Answers
3
You could try splitting this into two parts returning the latter part as upper. Not sure how to nest the upper inside the regex in Gsheets.
=REGEXEXTRACT(A1,"stri") & upper(REGEXEXTRACT(A1, "(ng)"))
this returned for me:
string > striNG
it will highly depend on actual data, but these works too for string
string
=REGEXREPLACE("stri"&UPPER("ng"); "(ng)"; "$1")
=REGEXREPLACE("string";"(n)";"")&
UPPER(REGEXREPLACE("string";"([a-z]4)";"")
=LEFT(REGEXREPLACE("string";"(w)";"$1");4)&
UPPER(RIGHT(REGEXREPLACE("string";"(w)";"$1");2))
all returns: striNG
striNG
this will replace ng with NG in the word string or any other word consisting of ng
ng
NG
string
ng
=REGEXEXTRACT(REGEXREPLACE("string"; "(ng)"; "NG"); "[a-zA-Z]1,")
this formula will do the same, but it uses UPPER() to do so:
UPPER()
=REGEXEXTRACT(REGEXREPLACE("string"; "(ng)"; UPPER("ng")); "[a-zA-Z]1,")
this formula provides a little bit more flexibility by using RIGHT() and specifying capital characters:
RIGHT()
=REGEXEXTRACT(REGEXREPLACE("string"; "(ng)"; RIGHT(UPPER("string"); 2)); "[a-zA-Z]1,")
and this final formula is able to take any word and also it's fully adjustable by specifying how many characters from the right side to the end of the word should be CAPSed:
CAPS
=REGEXEXTRACT(REGEXREPLACE("string";
RIGHT("string"; 2);
RIGHT(UPPER("string"); 2)); "[a-zA-Z]1,")
Thanks for contributing an answer to Web Applications Stack Exchange!
But avoid …
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
