Excel BI - Excel Challenge 744

excel-challenges

excel-formulas

🔰 Strings Expected Answer a4jtr a4Jtr wqt8u m5a2yq m5a2Yq x1ioudy x1iouDy 1ayoziiop

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 744

Challenge Description

🔰 Strings Expected Answer a4jtr a4Jtr wqt8u m5a2yq m5a2Yq x1ioudy x1iouDy 1ayoziiop

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/744/744 Capitalize the Consonant After a Vowel.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10")

pattern = "(?<=([aeiou])[0-9]?)([b-df-hj-np-tv-z])"

result = input %>%
  mutate(`Expected Answer` = map_chr(Strings, ~ {
    str_replace_all(.x, pattern, function(x) {
      toupper(x)
    })
  }))

all.equal(result$`Expected Answer`, test$`Expected Answer`, check.attributes = FALSE)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import re

path = "700-799/744/744 Capitalize the Consonant After a Vowel.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)
pattern = r'([aeiou][0-9]?)([b-df-hj-np-tv-z])'

def capitalize_consonant(s):
    return re.sub(pattern, lambda m: m.group(1) + m.group(2).upper(), s, flags=re.IGNORECASE)

input['Expected Answer'] = input.iloc[:,0].apply(capitalize_consonant)
input.drop(columns=input.columns[0], inplace=True)

print(input.equals(test)) # True

The Python version expresses the core extraction rule directly and keeps the pattern matching easy to review.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.