πŸ“… The Google Sheets REGEX Formula Cookbook

RSVPed Attending 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.

2 responses on “πŸ“… The Google Sheets REGEX Formula Cookbook”

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *