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.

How can I make REGEXREPLACE output the expected result?

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

2

it will highly depend on actual data, but these works too for 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

1

this will replace ng with NG in the word string or any other word consisting of ng

=REGEXEXTRACT(REGEXREPLACE("string"; "(ng)"; "NG"); "[a-zA-Z]{1,}")

this formula will do the same, but it uses UPPER() to do so:

=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:

=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:

=REGEXEXTRACT(REGEXREPLACE("string";
                     RIGHT("string";  2);
               RIGHT(UPPER("string"); 2)); "[a-zA-Z]{1,}")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.