Extract the LAST Correct Order These are numbers/symbols following the last alphabet letter. Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution Check the comment to download the workbook.
📌 Challenge Details and Links
Challenge Number: 30
Challenge Difficulty: ⭐⭐
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Numbers After The Last alphabet with Power Query
Power Query solution 1 for Extract Numbers After The Last alphabet, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Return = Table.AddColumn(
Source,
"Last Correct Order",
each [
S = Splitter.SplitTextByCharacterTransition({"A" .. "Z", "a" .. "z"}, {"#", "0" .. "9"})(
[#"Customers & Orders"]
),
R = List.Select(S, (f) => f = Text.Select(f, {"#", "0" .. "9"})){0}?
][R]
)
in
ReturnPower Query solution 2 for Extract Numbers After The Last alphabet, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Last Correct Order", each
if List.AnyTrue(List.Transform({"rejected", "not yet confirmed", "to confirm"},
(x)=> Text.Contains (Text.Lower([#"Customers & Orders"]), x))) then null else
List.Last(List.Select(Text.SplitAny(Text.Lower([#"Customers & Orders"]), Text.Combine({"a".."z"})), each _<>"")))
in
Sol
No me hace mucho sentido que, por ejemplo, la última fila, "Orders #564 and #5862 were rejected", lo que haga que se excluya sea los espacios anteriores al #, cuando realmente lo que indica que no es una orden correcta es la palabra "rejected", y si dijera, en lugar de "rejected", "correct"??Power Query solution 3 for Extract Numbers After The Last alphabet, proposed by Brian Julius:
let
Source = Table1,
Rscript = Table.SelectRows(
Table.ExpandTableColumn(
Table.RemoveColumns(
R.Execute(
"library(stringr)#(lf)df <- dataset#(lf)df$Answer <- str_match(df$CustomerOrders, ""(?<=[a-zA-Z])([#0-9-]+)$"")#(lf)df",
[dataset = Source]
),
"Name"
),
"Value",
{"CustomerOrders", "Answer"},
{"Customers & Orders", "Last Correct Order"}
),
each [#"Customers & Orders"] <> null
)
in
RscriptSolving the challenge of Extract Numbers After The Last alphabet with Excel
Excel solution 1 for Extract Numbers After The Last alphabet, proposed by Bo Rydobon 🇹🇭:
=IFNA(
REGEXEXTRACT(
C3:C8,
"#?d+$"),
"")Excel solution 2 for Extract Numbers After The Last alphabet, proposed by محمد حلمي:
=IFNA(
-LOOKUP(
0,
-RIGHT(
C3,
ROW(
$1:$9))),
"")Excel solution 3 for Extract Numbers After The Last alphabet, proposed by Julian Poeltl:
=MAP(
C3:C8,
LAMBDA(
C,
TAKE(
TEXTSPLIT(
C,
CHAR(
64+SEQUENCE(
26)),
,
,
1),
,
-1)))Excel solution 4 for Extract Numbers After The Last alphabet, proposed by Hussein SATOUR:
=IFNA(
REGEXEXTRACT(
C3:C8,
"[^a-zA-Z]+$"),
"")Excel solution 5 for Extract Numbers After The Last alphabet, proposed by Sunny Baggu:
=MAP(
C3:C8,
LAMBDA(
x,
TAKE(
TEXTSPLIT(
UPPER(
x),
,
CHAR(
SEQUENCE(
26,
,
CODE(
"A"))),
1),
-1)
)
)Excel solution 6 for Extract Numbers After The Last alphabet, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
C3:C8,
LAMBDA(
f,
TEXTAFTER(
f,
TAKE(
TOCOL(
TEXTSPLIT(
f,
ROW(
1:10)-1,
"#",
1)),
-1))))Excel solution 7 for Extract Numbers After The Last alphabet, proposed by Meganathan Elumalai:
=MID(
$B4,
MAX(
ISERROR(
MID(
$B4,
ROW(
INDIRECT(
"1:"&LEN(
$B4))),
1)*1)*ROW(
INDIRECT(
"1:"&LEN(
$B4))))+1,
25)Excel solution 8 for Extract Numbers After The Last alphabet, proposed by JvdV –:
=REGEXEXTRACT(
C3:C8,
".*[a-z](#?d+)",
2)Excel solution 9 for Extract Numbers After The Last alphabet, proposed by Milan Shrimali:
=MAP(
A2:A4,
LAMBDA(
x,
TAKE(
TEXTSPLIT(
x,
CHAR(
VSTACK(
SEQUENCE(
26,
,
97),
SEQUENCE(
26,
,
65)))),
,
-1)))Excel solution 10 for Extract Numbers After The Last alphabet, proposed by Shimi Naim:
IFERROR(
--TEXTSPLIT(
TEXTAFTER(
A2:A7),
"(",
SEQUENCE(
,
3)),
")"),
"")Excel solution 11 for Extract Numbers After The Last alphabet, proposed by Farzad Heidary:
MAP(
c3:c8,
LAMBDA(
x,
TAKE(
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
26,
,
65)),
,
1,
1),
,
-1)))Solving the challenge of Extract Numbers After The Last alphabet with Python
Python solution 1 for Extract Numbers After The Last alphabet, proposed by Konrad Gryczan, PhD:
Validation "by eye".
import pandas as pd
import re
import numpy as np
input = pd.read_excel('files/Excel Challenge 2nd June.xlsx', usecols='C', skiprows=1, nrows = 6)
test = pd.read_excel('files/Excel Challenge 2nd June.xlsx', usecols='D', skiprows=1, nrows = 6)
test['Last Correct Order'] = test['Last Correct Order'].replace(np.nan, '', regex=True)
def extract_after_last_letter(s):
match = list(re.finditer(r'[A-Za-z]', s))
if match:
last_letter_pos = match[-1].end()
return s[last_letter_pos:] if last_letter_pos < len(s) else ''
return ''
input['answer'] = input['Customers & Orders'].apply(extract_after_last_letter)
print(input.merge(test, left_index=True, right_index=True))Solving the challenge of Extract Numbers After The Last alphabet with Python in Excel
Python in Excel solution 1 for Extract Numbers After The Last alphabet, proposed by Abdallah Ally:
import pandas as pd
import re
file_path = 'Easy Excel Challenge 2nd June.xlsx'
df = pd.read_excel(file_path, usecols='C:D', skiprows=1, nrows=6)
df = df.replace(float('nan'), '')
# Perform data wrangling
def extract_last_order(text):
matches = re.findall('[a-zA-Z](#?d+)', text)
return matches[-1] if matches else ''
df['My Last Correct Order'] = df.iloc[:, 0].map(extract_last_order)
dfSolving the challenge of Extract Numbers After The Last alphabet with R
R solution 1 for Extract Numbers After The Last alphabet, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/Excel Challenge 2nd June.xlsx", range = "C2:C8")
test = read_excel("files/Excel Challenge 2nd June.xlsx", range = "D2:D8")
result = input %>%
mutate(last_letter = map_int(str_locate_all(`Customers & Orders`, "[A-Za-z]"), ~max(.x[,2]))) %>%
mutate(answer = ifelse(last_letter < nchar(`Customers & Orders`), str_sub(`Customers & Orders`, last_letter + 1), NA_character_))
identical(result$answer, test$`Last Correct Order`)
#> [1] TRUE