'Convert all UPPERCASE to Capitalize in Google Sheet

I have a Google Sheet with multiple rows of large text blocks. Within the text, there are heading tags that contain phrases that are in ALL CAPS. For example,

<p>Some paragraph text.</p><h2>THIS IS MY HEADING</h2><p>More paragraph text.</p>

I need to isolate the text strings within h2 tags and convert to Capitalize instead of UPPERCASE. I'm struggling to find a good formula and/or regex find/replace to accomplish this. Any feedback would be much appreciated.

regexgoogle-sheets-formulafind-replace


Solution 1:[1]

Try

=PROPER(LOWER(regexextract(A1,"h2>(.*)<\/h2")))

enter image description here

Solution 2:[2]

  1. Extract text.
=PROPER(REGEXEXTRACT(A1, "<h2>(.*)<\/h2>"))

enter image description here

  1. Replace and capitalize.
=REGEXREPLACE(A1, "<h2>.*<\/h2>", TEXTJOIN(B1,FALSE, "<h2>","</h2>"))

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mike Steelson
Solution 2

Related Questions