Extract zip code (5 or 6 digits in length) and country names.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 608
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Zip and Country Info with Power Query
Power Query solution 1 for Extract Zip and Country Info, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Zip",
each Number.From(
List.Last(
List.Select(
Text.Split(Text.Select([String], {"0" .. "9", ","}), ","),
(x) => Text.Length(x) > 4
)
)
)
),
C = Table.AddColumn(
B,
"Country",
each List.Select(
Xml.Tables(Web.Contents("https://api.worldbank.org/v2/country?per_page=300"))[country]{0}[
name
],
(y) => Text.Contains([String], y)
){0}
)
in
CPower Query solution 2 for Extract Zip and Country Info, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Zip",
each Number.From(
List.Last(
List.Select(
Text.Split(Text.Select([String], {"0" .. "9", ","}), ","),
each Text.Length(_) > 4
)
)
)
),
C = Table.AddColumn(
B,
"Country",
each
let
a = Text.Trim(
List.Last(
List.Transform(Text.Split([String], ", "), each Text.Select(_, {"A" .. "z", " "}))
)
),
b = if List.Count(Text.Split(a, " ")) > 2 then Text.AfterDelimiter(a, " ") else a,
c = Text.AfterDelimiter(b, " "),
d = if c = "Japan" then c else b
in
d
)
in
CPower Query solution 3 for Extract Zip and Country Info, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = Text.SplitAny([String], ", "),
b = List.Last(Text.PositionOf([String], ", ", 2)) + 2,
c = Text.Select(Splitter.SplitTextByPositions({b})([String]){0}, {"A" .. "z", " "}),
d =
if Text.Contains(c, "Japan") then
"Japan"
else if Text.Contains(c, "United Kingdom") then
"United Kingdom"
else
Text.Trim(c),
e = List.Select(
a,
each List.ContainsAny({"0" .. "9"}, Text.ToList(_)) and Text.Length(_) > 4
),
f = Table.FromRows({{Number.From(List.Last(e)), d}}, {"Zip", "Country"})
in
f
)[A]
)
in
SolPower Query solution 4 for Extract Zip and Country Info, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Data",
each [
a = Text.ToList([String]),
b = List.Transform(a, each if List.Contains({"0" .. "9"}, _) then _ else " "),
c = List.Last(List.Select(Text.Split(Text.Combine(b), " "), each Text.Length(_) > 4)),
d = Text.Split(List.Last(Text.Split([String], ", ")), " "),
e = Text.Trim(Text.Combine(List.LastN(d, 2), " ")),
f = if Text.Contains(e, "Japan") then "Japan" else e,
g = [Zip = Number.From(c), Country = f]
][g]
),
Result = Table.ExpandRecordColumn(AddCol, "Data", {"Zip", "Country"})[[Zip], [Country]]
in
ResultPower Query solution 5 for Extract Zip and Country Info, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
LZ = List.Zip,
a = LT(S[String], each Text.SplitAny(_, ",-")),
b = LT(a, each Text.Split(List.Last(_), " ")),
c = LT(
b,
each
if List.ContainsAny(_, {"New", "United"}) then
Text.Combine(List.LastN(_, 2), " ")
else
List.Last(_)
),
d = List.Select(LZ({List.Positions(c), c}), each List.Contains(_, "Canada")){0}{0},
e = LT(a, each List.Combine(LT(_, each Text.Split(_, " ")))),
f = LT(e, each List.Sort(List.RemoveNulls(LT(_, each try Number.From(_) otherwise null)))),
g = LT(LZ({List.Positions(f), f}), each if _{0} = d then List.Reverse(_{1}) else _{1}),
Sol = Table.FromRows(LZ({LT(g, each _{1}), c}), {"Zip", "Country"})
in
SolPower Query solution 6 for Extract Zip and Country Info, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Z = Table.AddColumn(
Source,
"Zip",
each List.Last(
List.Select(
Text.Split(Text.Trim(Text.Select([String], {"0" .. "9", " "})), " "),
(x) => Text.Length(x) >= 5
)
)
),
C = Table.AddColumn(
Z,
"Country",
each [
a = Text.Trim(Text.Select(List.Last(Text.Split([String], ",")), {"A" .. "z", " "})),
b = if Text.Contains(a, "Japan") then "Japan" else a
][b]
)
in
CSolving the challenge of Extract Zip and Country Info with Excel
Excel solution 1 for Extract Zip and Country Info, proposed by Bo Rydobon 🇹🇭:
=REGEXEXTRACT(
A3:A12,
{"d{5,}(?!.*d{5,})",
"(w+[wd] )?w+$"}
)Excel solution 2 for Extract Zip and Country Info, proposed by Bo Rydobon 🇹🇭:
=REGEXEXTRACT(
A3:A12,
{"d{5,}(?!.*d{5,})",
"(w+[wd] )?w+$"}
)Excel solution 3 for Extract Zip and Country Info, proposed by John V.:
=REGEXEXTRACT(A3:A12,{"d+(?!.*d{5})","([UN]w+ )?w+$"})Excel solution 4 for Extract Zip and Country Info, proposed by Kris Jaganah:
=MAP(A3:A12,LAMBDA(x,LET(a,--TEXTSPLIT(x,,{" ",","}),TAKE(TOCOL(a/(LEN(a)>4),3),-1))))Excel solution 5 for Extract Zip and Country Info, proposed by Julian Poeltl:
=REDUCE(
HSTACK(
"Zip",
"Country"
),
A3:A12,
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
LET(
SP,
TOROW(
--TEXTSPLIT(
B,
{" ",
","}
),
3
),
TAKE(
FILTER(
SP,
LEN(
SP
)>4
),
,
-1
)
),
TEXTAFTER(
B,
" ",
LEN(
B
)-SUBSTITUTE(
LEN(
B
)+IF(
OR(
ISNUMBER(
SEARCH(
"United",
B
)
),
ISNUMBER(
SEARCH(
"New",
B
)
)
),
2,
1
),
" ",
""
)
)
)
)
)
)Excel solution 6 for Extract Zip and Country Info, proposed by Aditya Kumar Darak 🇮🇳:
=REGEXEXTRACT(A3:A12, {"d{5,6}+","(w+[wd] )?w+$"})Excel solution 7 for Extract Zip and Country Info, proposed by Timothée BLIOT:
=HSTACK(
--MAP(
A3:A12,
LAMBDA(
z,
TAKE(
REGEXEXTRACT(
z,
"d{5,}",
1
),
,
-1
)
)
),
MAP(
A3:A12,
LAMBDA(
z,
REGEXEXTRACT(
z,
"((New|United)s)?w+$",
1
)
)
)
)Excel solution 8 for Extract Zip and Country Info, proposed by Hussein SATOUR:
=LET(c,TOCOL(TEXTBEFORE(TEXTSPLIT(WEBSERVICE("https://gist.githubusercontent.com/keeguon/2310008/raw/bdc2ce1c1e3f28f9cab5b4393c7549f38361be4e/countries.json"),,": '"),"',"),3),DROP(REDUCE("",A3:A12,LAMBDA(x,y,VSTACK(x,HSTACK(TAKE(REGEXEXTRACT(y,"bd{5,}b",1),,-1),FILTER(c,NOT(ISERR(FIND(c,y)))))))),1))Excel solution 9 for Extract Zip and Country Info, proposed by Sunny Baggu:
=MAP(
A3:A12,
LAMBDA(t,
LET(
_ts,
TEXTSPLIT(
t,
,
{", ",
",",
" "}
),
_l,
LEN(
_ts
),
_f,
FILTER(_ts,
(_l = 5) + (_l = 6)),
TAKE(
FILTER(
_f,
ISNUMBER(
--_f
)
),
-1
)
)
)
)Excel solution 10 for Extract Zip and Country Info, proposed by Md. Zohurul Islam:
=LET(
data,
A3:A12,
P,
MAP(
data,
LAMBDA(
p,
LET(
a,
IFERROR(
ABS(
TEXTSPLIT(
p,
{" ",
","}
)
),
0
),
b,
ABS(
LEN(
a
)>4
),
d,
FILTER(
a,
b
),
e,
TAKE(
d,
,
-1
),
e
)
)
),
S,
REDUCE(
"Country",
data,
LAMBDA(
y,
x,
LET(
a,
HSTACK(
"Kingdom",
"States",
"Zealand"
),
b,
TEXTSPLIT(
x,
,
{" ",
","}
),
c,
TAKE(
b,
-1
),
d,
TEXTJOIN(
" ",
,
TAKE(
b,
-2
)
),
e,
SUM(
ABS(
c=a
)
),
f,
IF(
e=0,
c,
d
),
g,
VSTACK(
y,
f
),
g
)
)
),
U,
HSTACK(
VSTACK(
"Zip",
P
),
S
),
U
)Excel solution 11 for Extract Zip and Country Info, proposed by Pieter de B.:
=HSTACK(MAP(A3:A12,LAMBDA(a,MAX(IFERROR(--TEXTSPLIT(a,{","," "}),0)))),TRIM(TEXTSPLIT(TEXTAFTER(A3:A12," ",-2),VSTACK(SEQUENCE(10)-1,","),,1)))Excel solution 12 for Extract Zip and Country Info, proposed by Hamidi Hamid:
=LET(
x,
IFERROR(
DROP(
REDUCE(
0,
A3:A12,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
{", ",
" "}
)
)
)
),
1
)*1,
""
),
BYROW(
MAP(
x,
LAMBDA(
a,
IF(
LEN(
a
)>=5,
a,
""
)
)
),
LAMBDA(
a,
LOOKUP(
9^9,
a
)
)
)
)Excel solution 13 for Extract Zip and Country Info, proposed by JvdV –:
=HSTACK(REGEXEXTRACT(A3:A12,".*bKd{5,}"),TRIM(REDUCE("",{1,2,3},LAMBDA(x,y,LET(s,TEXTAFTER(A3:A12," ",-y),IF(ISERR(WEBSERVICE("https://restcountries.com/v3.1/name/"&s)),x,s))))))Excel solution 14 for Extract Zip and Country Info, proposed by Edwin Tisnado:
=LET(d,A3:A12,l,TEXTAFTER,HSTACK(MAP(d,LAMBDA(x,LET(t,TEXTSPLIT(x,{" ",", "}),k,(LEN(t)>4)*t,XLOOKUP(9,k,k,,1)))),IF(ISERROR(l(d,{"New ","United"})),l(d," ",-1),l(d," ",-2))))Excel solution 15 for Extract Zip and Country Info, proposed by Philippe Brillault:
=LET(
Tb_Pattern,
{"1*(d{5,6})[A-z0-9,]*(?=[,|]sw*s?w*s?w+$)",
"((?:United|New)*[ABCFGIJKSZ]w+)$"},
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
_T
)
),
LAMBDA(
a,
i,
VSTACK(
a,
REGEXEXTRACT(
INDEX(
_T,
i
),
Tb_Pattern,
2
)
)
)
),
1
)
)
(NB: the Zip code is not easy to get)Excel solution 16 for Extract Zip and Country Info, proposed by Songglod P.:
=REDUCE(
{"Zip",
"Country"},
A3:A12,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
t,
TEXTSPLIT(
v,
{" ",
", "},
,&
1
),
c,
TAKE(
t,
,
-1
),
z,
TOROW(
--t,
3
),
HSTACK(
TAKE(
FILTER(
z,
LEN(
z
)>4
),
,
-1
),
SWITCH(
TRUE,
OR(
c="Kingdom",
c="States"
),
"United "&c,
c="Zealand",
"New "&c,
c
)
)
)
)
)
)Excel solution 17 for Extract Zip and Country Info, proposed by abdelaziz kamal allam:
=MAX(
LET(
a,
--TEXTSPLIT(
A3,
{",",
", ",
"-",
" "}
),
FILTER(
a,
ISNUMBER(
a
)
)
)
)Excel solution 18 for Extract Zip and Country Info, proposed by Britt Deaton, FSA:
=LET(
split_data,SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A3," "),",",""),"-",""),
num_5dig,(--ISNUMBER(VALUE(split_data)))*(LEN(split_data)>=5),
zip,TAKE(FILTER(split_data,num_5dig),,-1),
country1,TEXTAFTER(TEXTAFTER(A3,", ",-1)," ",-1,,1),
country2,TEXTAFTER(TEXTAFTER(A3,", ",-1)," ",-2,,1),
country,TRIM(IFERROR(IF(OR(LEFT(country2)="U",LEFT(country2)="N"),country2,country1),country1)),
HSTACK(zip,country))Solving the challenge of Extract Zip and Country Info with Python
Python solution 1 for Extract Zip and Country Info, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
from country_list import countries_for_language
path = "608 Extract Zip and Country.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=10)
def is_country(name):
return name in dict(countries_for_language('en')).values()
def extract_zip_country(string):
zip_code = re.search(r"(?Python solution 2 for Extract Zip and Country Info, proposed by Abdallah Ally:
import pandas as pd
import pycountry
import re
file_path = 'Excel_Challenge_608 - Extract Zip and Country.xlsx'
df = pd.read_excel(file_path, usecols='A', skiprows=1)
# Perform data manipulation
countries = [country.name for country in pycountry.countries]
df['Zip'] = df['String'].map(lambda x: re.findall('d{5,}', x)[-1])
df['Country'] = df['String'].map(lambda x: [c for c in countries if c in x][-1])
df = df[['Zip', 'Country']]
print(df)
Solving the challenge of Extract Zip and Country Info with Python in Excel
Python in Excel solution 1 for Extract Zip and Country Info, proposed by Alejandro Campos:
import re
df = xl("A2:A12", headers=True)
zip_code_pattern = r'bd{5}(?:-d{4})?b'
country_pattern = r'b(?:Germany|Japan|Canada|India|Australia|France|United Kingdom|Brazil|United States|New Zealand)b'
def extract_zip_and_country(text):
zip_code = re.findall(zip_code_pattern + r'|bd{6}b', text)
country = re.findall(country_pattern, text)
return zip_code[-1] if zip_code else None, country[0] if country else None
df[['Zip Code', 'Country']] = df['String'].apply(lambda x: pd.Series(extract_zip_and_country(x)))
df.drop(columns=['String'], inplace=True)
df
Solving the challenge of Extract Zip and Country Info with R
R solution 1 for Extract Zip and Country Info, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(countries)
path = "Excel/608 Extract Zip and Country.xlsx"
input = read_excel(path, range = "A2:A12")
test = read_excel(path, range = "B2:C12")
result = input %>%
mutate(Zip = str_extract(String, "(?<=\p{Punct}\s|^)\d{5,6}(?=,|\s{2})") %>% as.numeric(),
Country = str_extract(String, "(\w+)? \w+$") %>% str_trim(),
is_country = is_country(Country)) %>%
mutate(Country = ifelse(!is_country, str_extract(String, "\w+$") %>% str_trim(), Country)) %>%
select(Zip, Country)
all.equal(result, test)
# [1] TRUE
&&