How can I use the capturing group in a function?

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

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

google-sheets formulas regex

share|improve this question

edited Sep 2 at 18:38

user0

3,6434726

asked Sep 2 at 17:25

snazzybouche

1064

add a comment | 

up vote
1
down vote

favorite

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

google-sheets formulas regex

share|improve this question

edited Sep 2 at 18:38

user0

3,6434726

asked Sep 2 at 17:25

snazzybouche

1064

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

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

google-sheets formulas regex

share|improve this question

edited Sep 2 at 18:38

user0

3,6434726

asked Sep 2 at 17:25

snazzybouche

1064

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

google-sheets formulas regex

share|improve this question

edited Sep 2 at 18:38

user0

3,6434726

asked Sep 2 at 17:25

snazzybouche

1064

share|improve this question

share|improve this question

edited Sep 2 at 18:38

user0

3,6434726

edited Sep 2 at 18:38

user0

3,6434726

edited Sep 2 at 18:38

user0

3,6434726

3,6434726

asked Sep 2 at 17:25

snazzybouche

1064

asked Sep 2 at 17:25

snazzybouche

1064

asked Sep 2 at 17:25

snazzybouche

1064

1064

add a comment | 

add a comment | 

3 Answers
3

active

oldest

votes

up vote
3
down vote

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

share|improve this answer

edited Sep 2 at 20:03

answered Sep 2 at 19:31

user0

3,6434726

add a comment | 

up vote
2
down vote

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

share|improve this answer

answered Sep 2 at 19:23

Datanovice

1386

add a comment | 

up vote
1
down vote

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,")

share|improve this answer

answered Sep 2 at 21:21

user0

3,6434726

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "34"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fwebapps.stackexchange.com%2fquestions%2f120230%2fhow-can-i-use-the-capturing-group-in-a-function%23new-answer', 'question_page');

);

Post as a guest

Name Email

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
3
down vote

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

share|improve this answer

edited Sep 2 at 20:03

answered Sep 2 at 19:31

user0

3,6434726

add a comment | 

up vote
3
down vote

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

share|improve this answer

edited Sep 2 at 20:03

answered Sep 2 at 19:31

user0

3,6434726

add a comment | 

up vote
3
down vote

up vote
3
down vote

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

share|improve this answer

edited Sep 2 at 20:03

answered Sep 2 at 19:31

user0

3,6434726

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

share|improve this answer

edited Sep 2 at 20:03

answered Sep 2 at 19:31

user0

3,6434726

share|improve this answer

share|improve this answer

edited Sep 2 at 20:03

edited Sep 2 at 20:03

edited Sep 2 at 20:03

answered Sep 2 at 19:31

user0

3,6434726

answered Sep 2 at 19:31

user0

3,6434726

answered Sep 2 at 19:31

user0

3,6434726

3,6434726

add a comment | 

add a comment | 

up vote
2
down vote

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

share|improve this answer

answered Sep 2 at 19:23

Datanovice

1386

add a comment | 

up vote
2
down vote

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

share|improve this answer

answered Sep 2 at 19:23

Datanovice

1386

add a comment | 

up vote
2
down vote

up vote
2
down vote

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

share|improve this answer

answered Sep 2 at 19:23

Datanovice

1386

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

share|improve this answer

answered Sep 2 at 19:23

Datanovice

1386

share|improve this answer

share|improve this answer

answered Sep 2 at 19:23

Datanovice

1386

answered Sep 2 at 19:23

Datanovice

1386

answered Sep 2 at 19:23

Datanovice

1386

1386

add a comment | 

add a comment | 

up vote
1
down vote

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,")

share|improve this answer

answered Sep 2 at 21:21

user0

3,6434726

add a comment | 

up vote
1
down vote

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,")

share|improve this answer

answered Sep 2 at 21:21

user0

3,6434726

add a comment | 

up vote
1
down vote

up vote
1
down vote

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,")

share|improve this answer

answered Sep 2 at 21:21

user0

3,6434726

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,")

share|improve this answer

answered Sep 2 at 21:21

user0

3,6434726

share|improve this answer

share|improve this answer

answered Sep 2 at 21:21

user0

3,6434726

answered Sep 2 at 21:21

user0

3,6434726

answered Sep 2 at 21:21

user0

3,6434726

3,6434726

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fwebapps.stackexchange.com%2fquestions%2f120230%2fhow-can-i-use-the-capturing-group-in-a-function%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email