Google-sheets – How to use the capturing group in a function

formulasgoogle sheetsregex

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

Best Answer

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

Related Solutions

Google Sheets – Correct Syntax for IF Function

Remove the quotation marks in/around your expression:

=IF(PAGE!B10 <= 3, "Some words", "Some other words")

The IF function takes a boolean expression as its first parameter. In your formula, you are passing it a string (because of the quotation marks).

Google-sheets – “Split” delimited string, preserving blank entries

Try the following solution.

Formula

=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))

Explained

You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:
enter image description here

I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:
enter image description here

Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.

Result

enter image description here

Example

I've created an example file for you: SPLIT to preserve blank cells