Another series of 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. Extract last number from a string 2. Test if the string contains at least one occurrence of all 5 vowels 3. Check if password follows following rule – At least 8 characters long – At least one numeric, one small case one upper case and one special character – Contains no space
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 557
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Regex Password and Vowel Checks with Power Query
Power Query solution 1 for Regex Password and Vowel Checks, proposed by Ramiro Ayala Chávez:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S = Splitter.SplitTextByCharacterTransition,
T = List.Transform,
A = List.ContainsAny,
L = Text.ToList,
a = Source[String],
b = S({"a" .. "z"}, {"1" .. "9"})(a{0}),
c = List.Combine(T(b, S({"1" .. "9"}, {" "}))),
d = {List.Last(List.Select(T(c, each try Number.From(_) otherwise ""), each _ <> ""))},
e = T({a{1}} & {a{2}}, each List.ContainsAll(L(_), {"a", "e", "i", "o", "u"})),
f = T({33 .. 47}, Character.FromNumber),
g = T(
{a{3}} & {a{4}},
each Text.Length(_)
>= 8
and not Text.Contains(_, " ")
and A(L(_), {"1" .. "9"})
and A(L(_), {"A" .. "Z"})
and A(L(_), {"a" .. "z"})
and A(L(_), f)
),
Sol = Table.FromColumns({d & e & g}, {"Answer Expected"})
in
SolSolving the challenge of Regex Password and Vowel Checks with Excel
Excel solution 1 for Regex Password and Vowel Checks, proposed by Bo Rydobon 🇹🇭:
=REGEXEXTRACT(A2,"(d+)D*$",2)
2 =REGEXTEST(A3:A4,"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)",1)
3 =REGEXTEST(A5:A6,"^(?=.*p{Ll})(?=.*p{Lu})(?=.*d)(?=.*[W_])S{8,}$")Excel solution 2 for Regex Password and Vowel Checks, proposed by Rick Rothstein:
=0+TAKE(TEXTSPLIT(A2,CHAR(SEQUENCE(26,,97)),,1),,-1)
=AND(ISNUMBER(SEARCH({"a","e","i","o","u"},A3)))
=LET(x,CODE(MID(A7,SEQUENCE(LEN(A7)),1)),AND(LEN(A7)>7,OR(ABS(x-77.5)<13),OR(ABS(x-107.5)<13),OR(x<=65),AND(x<>32)))Excel solution 3 for Regex Password and Vowel Checks, proposed by John V.:
=REGEXEXTRACT(A2,"d+D*$")
✅=REGEXTEST(A3:A4,"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)")
✅=REGEXTEST(A5:A6,"(?=.*d)(?=.*[a-z])(?=.*[A-Z])(?=.*W)S{8,}$")Excel solution 4 for Regex Password and Vowel Checks, proposed by 🇰🇷 Taeyong Shin:
=REGEXEXTRACT(
A2,
"d+(?!.*d+)"
)
=REGEXTEST(
A3:A4,
"(?=.*?a)(?=.*?e)(?=.*?i)(?=.*?o)(?=.*?u)",
1
)
=REGEXTEST(
A5:A6,
"^(?=.{8,})(?=.*?d)(?=.*?p{Ll})(?=.*?p{Lu})(?=.*?[pPpS])S+$"
)Excel solution 5 for Regex Password and Vowel Checks, proposed by Kris Jaganah:
=VSTACK(
REGEXEXTRACT(
A2,
"[0-9]+$",
1
),
REGEXTEST(
A3:A4,
"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)",
1
),
REGEXTEST(
A5:A6,
"^(?=.*[a-z])(?=.*[A-Z])(?=.*d)(?=.*[W_])[^s]{8,}$",
1
)
)Excel solution 6 for Regex Password and Vowel Checks, proposed by Julian Poeltl:
=LET(
S,
--TEXTSPLIT(
A2,
CHAR(
SEQUENCE(
26,
,
97
)
)
),
TAKE(
FILTER(
S,
ISNUMBER(
S
)
),
,
-1
)
)
=PRODUCT(
LEN(
A3
)-LEN(
SUBSTITUTE(
A3,
{"a",
"e",
"i",
"o",
"u"},
""
)
)
)>0
=LET(T,
A5,
L,
LEN(
T
),
C,
CODE(
MID(
T,
SEQUENCE(
L
),
1
)
),
AND((L>7),
(NOT(
ISNUMBER(
SEARCH(
" ",
T
)
)
)),
L-SUM((C>47)*(C<58),
(C>64)*(C<91),
(C>96)*(C<123))))Excel solution 7 for Regex Password and Vowel Checks, proposed by Alejandro Campos:
=VSTACK(
--REGEXEXTRACT(
A2,
"(d+)D*$"
),
REGEXTEST(
A3:A6,
{"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)";"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)";"^(?=.*[a-z])(?=.*[A-Z])(?=.*d)(?=.*[@$!%*?&])[A-Za-zd@$!%*?&]{8,}$";"^(?=.*[a-z])(?=.*[A-Z])(?=.*d)(?=.*[@$!%*?&])[A-Za-zd@$!%*?&]{8,}$"}
)
)Excel solution 8 for Regex Password and Vowel Checks, proposed by Timothée BLIOT:
=REGEXEXTRACT(A2,"d+$")
(ii) =REGEXTEST(A3:A4,"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)")
(iii) =REGEXTEST(A5:A6,"(?=.*d+)(?=.*[a-z]+)(?=.*[A-Z]+)(?=.*[W_]+)(?!.*s+){8,}")Excel solution 9 for Regex Password and Vowel Checks, proposed by Hussein SATOUR:
=LET(r,REGEXEXTRACT, VSTACK(r(A2,"d+$"),MAP(A3:A4,LAMBDA(x,COUNTA(UNIQUE(TOCOL(r(LOWER(x),"[aeiou]",1))))=5)),REGEXTEST(A5:A6,"^(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z])(?=.*[@#$%^&+=])(?=S+$).{8,}$")))Excel solution 10 for Regex Password and Vowel Checks, proposed by Sunny Baggu:
=TAKE(
TEXTSPLIT(
A2,
TEXTSPLIT(
A2,
SEQUENCE(
10,
,
0
),
,
1
),
,
1
),
,
-1
)
=SUM(
--ISNUMBER(
SEARCH(
{"a";"e";"i";"o";"u"},
A3
)
)
)=5
=SUM(
--ISNUMBER(
SEARCH(
{"a";"e";"i";"o";"u"},
A4
)
)
)=5
=LET(
_a,
MID(
A5,
SEQUENCE(
LEN(
A5
)
),
1
),
_b,
CODE(
_a
),
AND(
LEN(
A5
) >= 8,
OR((_b >= CODE(
"A"
)) + (_b <= CODE(
"Z"
)) = 2),
OR((_b >= CODE(
"a"
)) + (_b <= CODE(
"z"
)) = 2),
OR(
_b <= 65
),
AND(
_b <> CODE(
" "
)
)
)
)
=LET(
_a,
MID(
A6,
SEQUENCE(
LEN(
A6
)
),
1
),
_b,
CODE(
_a
),
AND(
LEN(
A6
) >= 8,
OR((_b >= CODE(
"A"
)) + (_b <= CODE(
"Z"
)) = 2),
OR((_b >= CODE(
"a"
)) + (_b <= CODE(
"z"
)) = 2),
OR(
_b <= 65
),
AND(
_b <> CODE(
" "
)
)
)
)Excel solution 11 for Regex Password and Vowel Checks, proposed by Abdallah Ally:
=REGEXEXTRACT(A2,"d+(?!.*d)")
=REGEXTEST(A3:A4,"(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)")
=REGEXTEST(A5:A6,"^(?=.*[a-z])(?=.*[A-Z])(?=.*d)(?=.*[W_])[^s]{8,}$")Excel solution 12 for Regex Password and Vowel Checks, proposed by Md. Zohurul Islam:
=REGEXEXTRACT(A2, "(d+)(?!.*d)")
=REGEXTEST(A3:A4, "^(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)")
=REGEXTEST(A5:A6, "^(?=.*[a-z])(?=.*[A-Z])(?=.*d)(?=.*[W_])[^s]{8,}$")Excel solution 13 for Regex Password and Vowel Checks, proposed by JvdV -:
=LEN(
REGEXREPLACE(
A3:A4,
"([aeiou])(?!.*1)|.",
"$1"
)
)Solving the challenge of Regex Password and Vowel Checks with Python
Python solution 1 for Regex Password and Vowel Checks, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "557 Regex Challenges 2.xlsx"
input = pd.read_excel(path, usecols="A", nrows=6)
test = pd.read_excel(path, usecols="C", nrows=6)
test['Answer Expected'] = pd.to_numeric(test['Answer Expected'])
def extract_last_number(s):
match = re.search(r'd+(?!.*d)', s)
return int(match.group()) if match else None
def contains_all_vowels(s):
return all(vowel in s for vowel in 'aeiou')
def is_strong_password(s):
return bool(re.match(r'^(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[^A-Za-z0-9])(?=S+$).{8,}$', s))
q1 = input.iloc[[0]].copy()
q1['Answer'] = q1['String'].apply(extract_last_number)
q2 = input.iloc[[1, 2]].copy()
q2['Answer'] = q2['String'].apply(lambda x: int(contains_all_vowels(x)))
q3 = input.iloc[[3, 4]].copy()
q3['Answer'] = q3['String'].apply(lambda x: int(is_strong_password(x)))
answer = pd.concat([q1, q2, q3], ignore_index=True)
print(answer["Answer"].equals(test["Answer Expected"])) # True
Solving the challenge of Regex Password and Vowel Checks with Python in Excel
Python in Excel solution 1 for Regex Password and Vowel Checks, proposed by Alejandro Campos:
import re
df = xl("A1:B6", headers=True)
for i in range(1, len(df)):
if df.at[i, 'Challenge'] == 'SAME AS PREVIOUS':
df.at[i, 'Challenge'] = df.at[i - 1, 'Challenge']
def extract_last_number(s):
match = re.search(r'd+$', s)
return int(match.group()) if match else None
def contains_all_vowels(s):
return bool(re.search(r'(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)', s, re.IGNORECASE))
def check_password_rules(s):
password_pattern = r'^(?=.*[a-z])(?=.*[A-Z])(?=.*d)(?=.*[!@#$%^&*()_+{}:;<>,.?/~`[]|\]).{8,}$'
return bool(re.match(password_pattern, s))
df['Result'] = df.apply(
lambda row: (
extract_last_number(row['String']) if row['Challenge'] == 'Extract last number from a string' else
check_password_rules(row['String'])
),
axis=1
)
df = df.drop(columns=['Challenge'])
df
Solving the challenge of Regex Password and Vowel Checks with R
R solution 1 for Regex Password and Vowel Checks, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/557 Regex Challenges 2.xlsx"
input = read_excel(path, range = "A1:A6")
test = read_excel(path, range = "C1:C6") %>%
mutate(`Answer Expected` = as.numeric(`Answer Expected`))
q1 = input %>%
filter(row_number() == 1) %>%
mutate(Answer = str_extract(String, "\d+(?!.*\d)") %>% as.numeric())
q2 = input %>%
filter(row_number() %in% c(2, 3)) %>%
mutate(Answer = str_detect(String, "(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)") %>% as.numeric())
q3 = input %>%
filter(row_number() %in% c(4, 5)) %>%
mutate(Answer = str_detect(String, "^(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[^A-Za-z0-9])(?=\S+$).{8,}$") %>% as.numeric())
answer = bind_rows(q1, q2, q3)
all.equal(answer$Answer, test$`Answer Expected`, check.attributes = FALSE)
#> [1] TRUE
&&&