Home » Three Regex Pattern Tasks

Three Regex Pattern Tasks

This time 3 challenges into one to hone your regex skills. If you don’t have regex and or not at home with regex, keep posting your formula / language solutions. Generate 3 different regex formulas to – 1. Convert YYYYMMDD to MMDDYY 2. Extract first and last name and generate last name, first name 3. Make first and last letter of each words capital

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 552
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Three Regex Pattern Tasks with Power Query

Power Query solution 1 for Three Regex Pattern Tasks, proposed by Ramiro Ayala Chávez: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  L = List.Transform, 
  S = Text.Split, 
  C = Text.Combine, 
  a = Source[String], 
  b = S(Text.From(Date.From(a{0})), "/"), 
  c = L(
    b, 
    each 
      if Text.Length(_) = 1 then
        Text.Insert(_, 0, "0")
      else if Text.Length(_) = 4 then
        Text.RemoveRange(_, 0, 2)
      else
        _
  ), 
  d = c{1} & "-" & c{2} & "-" & c{0}, 
  e = S(a{1}, " "), 
  f = C({List.Last(e)} & {List.First(e)}, ", "), 
  g = L(S(a{2}, " "), Text.ToList), 
  h = L(
    g, 
    each {Text.Upper(_{0})} & List.RemoveFirstN(List.RemoveLastN(_)) & {Text.Upper(List.Last(_))}
  ), 
  i = C(L(h, each C(_)), " "), 
  Sol = Table.FromColumns({{d} & {f} & {i}}, {"Answer Expected"})
in
  Sol
Power Query solution 2 for Three Regex Pattern Tasks, proposed by Ahmed Ariem: 
let
  People = Text.Split(
    Text.FromBinary(
      Web.Contents(
        "https://gist.githubusercontent.com/azmaahmed/eb51ac2c96fbdbd7ca0e796063517499/raw/10fc8d1c87801846e27a37f7428576ebf501f97f/GetPeoplesName"
      )
    ), 
    ","
  ), 
  f = (x) =>
    [
      l = Text.Length(Text.Select(x, "-")) = 2, 
      a = Splitter.SplitTextByAnyDelimiter({" ", "-"})(x), 
      b = 
        if l then
          a{1} & "-" & a{2} & "-" & Text.End(a{0}, 2)
        else if List.ContainsAny(People, a) then
          a{2} & ", " & a{0}
        else
          Text.Combine(
            List.Transform(
              a, 
              (x) =>
                Text.Start(Text.Proper(x), Text.Length(x) - 1)
                  & Text.Proper(Text.End(Text.Proper(x), 1))
            ), 
            " "
          )
    ][b], 
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  to = Table.TransformColumns(Source, {"String", f})
in
  to
Power Query solution 3 for Three Regex Pattern Tasks, proposed by Ahmed Ariem: 
let
 f = (x) =>
 [
 l = Text.Length(Text.Select(x, "-")) = 2, 
 a = Splitter.SplitTextByAnyDelimiter({" ", "-"})(x), 
 b = 
 if l then
 a{1} & "-" & a{2} & "-" & Text.End(a{0}, 2)
 else if List.ContainsAny(People, a) then
 a{2} & ", " & a{0}
 else
 Text.Combine(
 List.Transform(
 a, 
 (x) =>
 Text.Start(Text.Proper(x), Text.Length(x) - 1)
 & Text.Proper(Text.End(Text.Proper(x), 1))
 ), 
 " "
 )
 ][b], 
 Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
 to = Table.TransformColumns(Source, {"String", f})
in
 to
----
attached file
https://1drv.ms/x/s!AiUZ0Ws7G26RkQuLBDSz59yhCfly?e=ufShNG

Solving the challenge of Three Regex Pattern Tasks with Excel

Excel solution 1 for Three Regex Pattern Tasks, proposed by Bo Rydobon 🇹🇭: 
=REGEXREPLACE(A2:A4,{"..(..)-(.*)";"(w+).*?(w+$)";"(w+)(w)"},{"$2-$1";"$2, $1";"u$1U$2"})
Excel solution 2 for Three Regex Pattern Tasks, proposed by Rick Rothstein: 
=VSTACK(TEXT(A2,"mm-dd-yy"),TEXTJOIN(", ",,CHOOSECOLS(TEXTSPLIT(A3," "),3,1)),TEXTJOIN(" ",,MAP(TEXTSPLIT(PROPER(A4)," "),LAMBDA(x,REPLACE(x,LEN(x),1,UPPER(RIGHT(x)))))))

Or as individual formulas...
In C2:
=TEXT(A2,"mm-dd-yy")

In C3:
=TEXTJOIN(", ",,CHOOSECOLS(TEXTSPLIT(A3," "),3,1))

In C4:
=TEXTJOIN(" ",,MAP(TEXTSPLIT(PROPER(A4)," "),LAMBDA(x,REPLACE(x,LEN(x),1,UPPER(RIGHT(x))))))
Excel solution 3 for Three Regex Pattern Tasks, proposed by Rick Rothstein: 
=TEXT(A2,"mm-dd-yy")

to this...

=0+A2

but now you will have to format cell A2 to change the date serial number (which will be returned from the formula)
Excel solution 4 for Three Regex Pattern Tasks, proposed by John V.: 
=REGEXREPLACE(A2:A4,{"..(..)-(.+)";"(w+) w+ (w+)";"(w)(w*)(w)"},{"$2-$1";"$2, $1";"u$1$2u$3"})
Excel solution 5 for Three Regex Pattern Tasks, proposed by 🇰🇷 Taeyong Shin: 
=REGEXREPLACE(
    A2,
     "d{2}(d{2})-([d-]+)",
     "$2-$1"
)
=REGEXREPLACE(
    A3,
     "(^w+).*?(w+$)",
     "$2, $1"
)
=REGEXREPLACE(
    A4,
     "b(w)(w+)?(w)b",
     "U$1L$2U$3"
)
Excel solution 6 for Three Regex Pattern Tasks, proposed by Kris Jaganah: 
=REGEXREPLACE(
    A2,
    "d{2}(d{2})-(d{2})-(d{2})",
    "$2-$3-$1"
)
Excel solution 7 for Three Regex Pattern Tasks, proposed by Julian Poeltl: 
=TEXT(
    A2*1,
    "MM-DD-YY"
)
=LET(
    S,
    TEXTSPLIT(
        A3,
        ,
        " "
    ),
    TAKE(
        S,
        -1
    )&", "&TAKE(
        S,
        1
    )
)
=LET(
    S,
    PROPER(
        TEXTSPLIT(
            A4,
            " "
        )
    ),
    TEXTJOIN(
        " ",
        ,
        LEFT(
            S,
            LEN(
                S
            )-1
        )&UPPER(
            RIGHT(
                S
            )
        )
    )
)
Excel solution 8 for Three Regex Pattern Tasks, proposed by Timothée BLIOT: 
=REGEXREPLACE(
    A2,
    "(d{2})(d{2})-(d{2})-(d{2})",
    "$3-$4-$2"
)
(ii) =REGEXREPLACE(
    A3,
    "(^[A-Za-z]+)(.*s)([A-Za-z]+$)",
    "$3, $1"
)
(iii) =REGEXREPLACE(
    A4,
    "([a-z]?b|b[a-z]?)",
    "U$1"
)
Excel solution 9 for Three Regex Pattern Tasks, proposed by Sunny Baggu: 
=VSTACK(
    
     TEXT(
         A2,
          "mm-dd-yy"
     ),
    
     TEXTAFTER(
         A3,
          " ",
          -1
     ) & ", " & TEXTBEFORE(
         A3,
          " "
     ),
    
     LET(
         
          a,
          TEXTSPLIT(
              A4,
               ,
               " "
          ),
         
          b,
          LEN(
              a
          ),
         
          TEXTJOIN(
              " ",
               ,
               PROPER(
                   LEFT(
                       a,
                        b - 1
                   )
               ) & UPPER(
                   RIGHT(
              a
          )
               )
          )
          
     )
    
)
Excel solution 10 for Three Regex Pattern Tasks, proposed by Abdallah Ally: 
=REGEXREPLACE(A2:A3,{".+(d{2})-(d+)-(d+)";"([a-z]+)s.+s([a-z]+)"},{"$2-$3-$1";"$2, $1"},,1)

For third value
=TEXTJOIN(" ",,MAP(TEXTSPLIT(A4," "),LAMBDA(x,PROPER(LEFT(x,LEN(x)-1))&UPPER(RIGHT(x)))))
Excel solution 11 for Three Regex Pattern Tasks, proposed by Abdallah Ally: 
=VSTACK(
    REGEXREPLACE(
        A2:A3,
        {".+(d{2})-(d+)-(d+)";"([a-z]+)s.+s([a-z]+)"},
        {"$2-$3-$1";"$2, $1"},
        ,
        1
    ),
    TEXTJOIN(
        " ",
        ,
        MAP(
            TEXTSPLIT(
                A4,
                " "
            ),
            LAMBDA(
                x,
                PROPER(
                    LEFT(
                        x,
                        LEN(
                            x
                        )-1
                    )
                )&UPPER(
                    RIGHT(
                            x
                        )
                )
            )
        )
    )
)
Excel solution 12 for Three Regex Pattern Tasks, proposed by Anshu Bantra: 
=VSTACK(
 REGEXREPLACE(A2, "d{2}(d+)-(d+)-(d+)", "$2-$3-$1"),
 REGEXREPLACE(A3, "(w+) (w+) (w+)", "$3, $1"),
 REGEXREPLACE(A4, "(w)(w*)(w)", "u$1$2u$3")
)
Excel solution 13 for Three Regex Pattern Tasks, proposed by Md. Zohurul Islam: 
=LET(
A,REGEXREPLACE(A2, "(d{4})-(d{2})-(d{2})", "$2-$3-" & MID(A2,3,2)),
B,REGEXREPLACE(A3, "(w+)s+(w+)s+(w+)", "$3, $1"),
C,REGEXREPLACE(A4, "(?i)(w)(w*)(w)", "U$1E$2U$3E"),
result,VSTACK(A,B,C),
result)
Excel solution 14 for Three Regex Pattern Tasks, proposed by Hamidi Hamid: 
=LET(x,TEXT(TEXTJOIN("/",,,CHOOSECOLS(TEXTSPLIT(A2,"-",,1),{231})),"jj-mm-aa"),y,LOOKUP("zzz",TEXTSPLIT(A3," ",,-1))&", "&TEXTBEFORE(A3," "),z,TEXTJOIN(" ",,MAP(TEXTSPLIT(A4," ",1),LAMBDA(a,UPPER(LEFT(a,1))&MID(LEFT(a,LEN(a)-1),2,LEN(a))&UPPER(RIGHT(a,1))))),VSTACK(x,y,z))
Excel solution 15 for Three Regex Pattern Tasks, proposed by JvdV –: 
=REGEXREPLACE(
    A2:A4,
    "(([A-Z]w*).* (w+)|d.(..)-(.+)|(w+?)(w)?b)",
    "$3$5${2:+, }${4:+-}$2$4u$6u$7"
)
Excel solution 16 for Three Regex Pattern Tasks, proposed by Eddy Wijaya: 
=VSTACK(
TEXT(A2,"mm-dd-yy"),
ARRAYTOTEXT(CHOOSECOLS(TEXTSPLIT(A3," "),-1,1)),
TEXTJOIN(" ",,BYROW(TEXTSPLIT(A4,," "),LAMBDA(r,DROP(REDUCE(0,r,LAMBDA(a,v,VSTACK(a,LET(
spl,MID(v,SEQUENCE(,LEN(v)),1),
TEXTJOIN("",,CHOOSECOLS(HSTACK(UPPER(TAKE(spl,,1)),DROP(spl,,1),UPPER(TAKE(spl,,-1))),SEQUENCE(COUNTA(spl)-1),-1)))))),1)))))
Excel solution 17 for Three Regex Pattern Tasks, proposed by RIJESH T.: 
=TEXT(A2,"mm-dd-yy")
=TEXTAFTER(A3," ",2)&", "&TEXTBEFORE(A3," ")
=TEXTJOIN(" ",,SUBSTITUTE(TEXTSPLIT(PROPER(A4)," "),RIGHT(TEXTSPLIT(PROPER(A4)," "),1),UPPER(RIGHT(TEXTSPLIT(PROPER(A4)," "),1)),{1,1,2}))
Excel solution 18 for Three Regex Pattern Tasks, proposed by Songglod P.: 
=TEXT(A2,"MM-DD-YY")

D3
=TEXTJOIN(", ",,TEXTAFTER(A3," ",-1),TEXTBEFORE(A3," "))

D4
=TEXTJOIN(" ",,MAP(TEXTSPLIT(A4," "),LAMBDA(w,REPLACE(PROPER(w),LEN(w),1,UPPER(RIGHT(w))))))
Excel solution 19 for Three Regex Pattern Tasks, proposed by Stefan Alexandrov: 
=VSTACK(
    TEXT(
        A2,
        "mm-dd-yy"
    ),
    
    TEXTJOIN(
        ", ",
        1,
        CHOOSECOLS(
            TEXTSPLIT(
                A3,
                " "
            ),
            3
        ),
        CHOOSECOLS(
            TEXTSPLIT(
                A3,
                " "
            ),
            1
        )
    ),
    
    TEXTJOIN(
        " ",
        1,
        LEFT(
            TEXTSPLIT(
                PROPER(
                    A4
                ),
                " "
            ),
            LEN(
                TEXTSPLIT(
                    PROPER(
                    A4
                ),
                    " "
                )
            )-1
        )&UPPER(
            RIGHT(
                TEXTSPLIT(
                    PROPER(
                    A4
                ),
                    " "
                ),
                1
            )
        )
        
    )
)
Excel solution 20 for Three Regex Pattern Tasks, proposed by Nonbow Wu: 
=VSTACK(
    
    REGEXREPLACE(
        A2,
        ".*(d{2})-(d+-d+)",
        "$2-$1"
    ),
    
    REGEXREPLACE(
        A3,
        "^(w+)b.+b(w+)$",
        "$2, $1"
    ),
    
    REGEXREPLACE(
        A4,
        "b(w)|(w)b",
        "U$0"
    )
    
)
Excel solution 21 for Three Regex Pattern Tasks, proposed by Md. Shah Alam, Microsoft Certified Trainer: 
=TEXT(
    A2,
    "mm-dd-yy"
)
2. =LET(
    x,
    TEXTSPLIT(
        A3,
        " "
    ),
    y,
    INDEX(
        x,
        COUNTA(
            x
        )
    ),
    z,
    INDEX(
        x,
        1
    ),
    y&","&z
)
3. =LET(
    x,
    TEXTSPLIT(
        A4,
        " "
    ),
    y,
    UPPER(
        LEFT(
            x
        )
    )&MID(
        x,
        2,
        LEN(
            x
        )-2
    )&UPPER(
        RIGHT(
            x
        )
    ),
    TEXTJOIN(
        " ",
        ,
        y
    )
)

Solving the challenge of Three Regex Pattern Tasks with Python

Python solution 1 for Three Regex Pattern Tasks, proposed by Konrad Gryczan, PhD: 
import pandas as pd
import re
path = "552 Regex Challenges.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=4)
test = pd.read_excel(path, usecols="C", nrows=4)
q1 = input.iloc[0:1].assign(Answer=lambda df: df['String'].str.replace(r"(d{2})(d{2})-(d{2})-(d{2})", r"3-4-2", regex=True))
q2 = input.iloc[1:2].assign(Answer=lambda df: df['String'].str.replace(r"^(w+) w+ (w+)$", r"2, 1", regex=True))
q3 = input.iloc[2:3].assign(Answer=lambda df: df['String'].str.replace(r"b(w)(w*?)(w)b", lambda m: m.group(1).upper() + m.group(2) + m.group(3).upper(), regex=True))
answers = pd.concat([q1, q2, q3])['Answer'].reset_index(drop=True)
print(answers.equals(test["Answer Expected"])) # True

Solving the challenge of Three Regex Pattern Tasks with Python in Excel

Python in Excel solution 1 for Three Regex Pattern Tasks, proposed by Alejandro Campos: 
import re
df = pd.DataFrame({
 "Original_String": ["2024-&11-03", "Donald John Trump", "excel is awesome"]
})
def convert_date(date_str):
 return re.sub(r'd{2}(d+)-(d+)-(d+)', r'2-3-1', date_str)
def name_swap(name_str):
 return re.sub(r'(w+)s+w+s+(w+)', r'2, 1', name_str)
def capitalize_first_last(word_str):
 return re.sub(r'b(w)(w*)(w)b', lambda m: m.group(1).upper() + m.group(2).lower() + m.group(3).upper(), word_str)
df['Answer_Expected'] = df['Original_String']
df.loc[0, 'Answer_Expected'] = convert_date(df.loc[0, 'Original_String']) # Para la fecha
df.loc[1, 'Answer_Expected'] = name_swap(df.loc[1, 'Original_String'])  # Para el nombre
df.loc[2, 'Answer_Expected'] = capitalize_first_last(df.loc[2, 'Original_String']) # Para las palabras
df[['Answer_Expected']]
Python in Excel solution 2 for Three Regex Pattern Tasks, proposed by Anshu Bantra: 
import re
txt = xl("A1:A4", headers=True)['String'].values
def capitalize_first_last(match):
 if len(word) > 1:
 return word[0].upper() + word[1:-1] + word[-1].upper()
 else:
 return word.upper()
patterns = [
 (r'd{2}(d{2})-(d{2})-(d{2})', r'2-3-1'),
 (r'(w+) (w+) (w+)', r'3, 1'),
 (r'(w)(w*)(w)', capitalize_first_last)
]
[re.sub(patterns[_][0], patterns[_][1], txt[_]) for _ in range(len(txt))]
Python in Excel solution 3 for Three Regex Pattern Tasks, proposed by Ümit Barış Köse, MSc: 
import re
def convert_yyyymmdd(date_string):
 match = re.match(r'(d{4})-(d{2})-(d{2})', date_string)
 return f"{match.group(2)}-{match.group(3)}-{match.group(1)[2:]}" if match else None
def extract_names(name_string):
 names = name_string.split()
 return f"{names[-1]}, " if len(names) >= 2 else None
def capitalize_ends(text):
df = xl("A2:A4")
functions = [convert_yyyymmdd, extract_names, capitalize_ends]
results = [func(df.iloc[i, 0]) for i, func in enumerate(functions)]
results

Solving the challenge of Three Regex Pattern Tasks with R

R solution 1 for Three Regex Pattern Tasks, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/552 Regex Challenges.xlsx"
input = read_excel(path, range = "A1:B4")
test = read_excel(path, range = "C1:C4")
q1 = input %>%
 filter(row_number() == 1) %>%
 mutate(Answer = str_replace(String, 
 "(\d{2})(\d{2})-(\d{2})-(\d{2})",
 "\3-\4-\2"))
q2 = input %>%
 filter(row_number() == 2) %>%
 mutate(Answer = str_replace(String, 
 "^(\w+) \w+ (\w+)$",
 "\2, \1"))
q3 = input %>%
 filter(row_number() == 3) %>%
 mutate(Answer = gsub("\b(\w)(\w*?)(\w)\b", 
 "\U\1\E\2\U\3", 
 String, perl = TRUE))
answers = bind_rows(q1, q2, q3) %>%
 select(Answer)
all.equal(answers, test, check.attributes = FALSE)
#> [1] TRUE

&&

Leave a Reply