π The Google Sheets REGEX Formula Cookbook
Enhance your data skills and learn how to use the powerful REGEX formulas in Google Sheets
Completed Ben Collins’ course on REGEX. As always, I appreciated the way in which Collins’ unpacks different scenarios. The only thing that I would have been interested in more discussion of using REGEX within a QUERY.
Amit Agarwal provides a list of Regex formulas to use to validate data within Google Forms.
Two solutions provided for bolding text in Google Sheets.
The first using SUBSTITUTE of one font to another:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E3,"A","π"),"B","π"),"C","π"),"D","π"),"E","π"),"F","π "),"G","π"),"H","π"),"I","π"),"J","π"),"K","π"),"L","π"),"M","π"),"N","π"),"O","π"),"P","π"),"Q","π"),"R","π"),"S","π"),"T","π"),"U","π"),"V","π"),"W","π"),"X","π"),"Y","π"),"Z","π"),"0","π"),"1","π"),"2","π"),"3","π"),"4","π"),"5","π"),"6","π"),"7","π"),"8","π"),"9","π")The second solution combines REGEX and UNICODE:
=ARRAYFORMULA(JOIN("", UNICHAR(QUERY(UNICODE(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(
REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2&""
,"([^a-zA-Z0-9])","$1"&UNICHAR(160)&UNICHAR(1)&CHAR(127))
,"'","''")
,"([a-z])","$1"&UNICHAR(160)&UNICHAR(119738)&CHAR(127))
,"([A-Z])","$1"&UNICHAR(160)&UNICHAR(119744)&CHAR(127))
,"([0-9])","$1"&UNICHAR(160)&UNICHAR(120735)&CHAR(127))
,"'","''")
,CHAR(127))), UNICHAR(160))), "select Col1+Col2-1 label Col1+Col2-1 ''",0))))
I prefer the second solution. However, the first is easier to apply to a whole column using the ARRAYFORMULA.