Write a formula to convert texts into numbers
📌 Challenge Details and Links
Challenge Number: 190
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert Text To Number! with Power Query
Power Query solution 1 for Convert Text To Number!, proposed by Brian Julius:
let
Source = RawData,
CallGPT4 = Table.AddColumn(
Source,
"Result",
each
let
TextToConvert = [Text],
apiKey = Text.FromBinary(File.Contents("C:tempPQ_API_KEY.txt")),
GPTPrompt = Json.FromValue(
[
model = "gpt-4",
messages = {
[
role = "system",
content
= "You are a helpful assistant that converts spelled-out English numbers to numeric values. Return only the numeric value."
],
[role = "user", content = TextToConvert]
},
temperature = 0
]
),
APICallResult = Json.Document(
Web.Contents(
"https://api.openai.com/v1/chat/completions",
[
Headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & apiKey
],
Content = GPTPrompt
]
)
),
ResponseContent = APICallResult[choices]{0}[message][content]
in
ResponseContent
)
in
CallGPT4Power Query solution 2 for Convert Text To Number!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ToNumbers = Table.AddColumn(Source, "Output", each
let
numWords = [zero=0, one=1, two=2, three=3, four=4, five=5, six=6, seven=7, eight=8, nine=9, ten=10, eleven=11, twelve=12, thirteen=13, fourteen=14, fifteen=15, sixteen=16, seventeen=17, eighteen=18, nineteen=19, twenty=20, thirty=30, forty=40, fifty=50, sixty=60, seventy=70, eighty=80, ninety=90, hundred=100, thousand=1000, million=1000000 ],
wordsList = Text.SplitAny(Text.Lower(Text.Trim([Date])), " -"),
result = List.Accumulate(
wordsList,
[total = 0, current = 0, lastMultiplier = 1],
(state, word) =>
let
value = Record.FieldOrDefault(numWords, word, null)
in
if value = null then state
else if value = 100 then
[total = state[total], current = state[current] * value, lastMultiplier = 100]
else if value = 1000 then
[total = state[total] + state[current] * value, current = 0, lastMultiplier = 000]
else if value = 1000000 then
[total = state[total] + state[current] * value, current = 0, lastMultiplier = 1000000]
else
[total = state[total], current = state[current] + value, lastMultiplier = 1]
),
finalNumber = result[total] + result[current]
in
finalNumber)[Output]
in
ToNumbersPower Query solution 3 for Convert Text To Number!, proposed by Yaroslav Drohomyretskyi:
let
WordsToNumber = (words as text) as number =>
let
numWords = [
zero = 0,
one = 1,
two = 2,
three = 3,
four = 4,
five = 5,
six = 6,
seven = 7,
eight = 8,
nine = 9,
ten = 10,
eleven = 11,
twelve = 12,
thirteen = 13,
fourteen = 14,
fifteen = 15,
sixteen = 16,
seventeen = 17,
eighteen = 18,
nineteen = 19,
twenty = 20,
thirty = 30,
forty = 40,
fifty = 50,
sixty = 60,
seventy = 70,
eighty = 80,
ninety = 90,
hundred = 100,
thousand = 1000,
million = 1000000,
billion = 1000000000
],
wordsList = Text.SplitAny(Text.Lower(Text.Trim(words)), " -"),
result = List.Accumulate(
wordsList,
[total = 0, current = 0],
(state, word) =>
let
value = Record.FieldOrDefault(numWords, word, null)
in
if value = null then
state
else if value >= 100 then
[total = state[total] + state[current] * value, current = 0]
else
[total = state[total], current = state[current] + value]
),
finalNumber = result[total] + result[current]
in
finalNumber,
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToNumbers = Table.AddColumn(Source, "Output", each WordsToNumber([Date]))[Output]
in
ToNumbersPower Query solution 4 for Convert Text To Number!, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
P = {"zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten",
"eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen",
"twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety",
"hundred", "thousand", "million", "billion"},
L = {0..20} & List.Numbers(30,8,10) & {1000, 1000000, 1000000000},
C = Table.FromColumns({Table.ToList(H, each try Expression.Evaluate(Text.Combine(List.Transform(Text.SplitAny(Text.Lower(_{0})," -"),
each let x = List.PositionOf(P,_), y = Text.From(L{x}) in if x < 28 then "+" & y else "*" & y))) otherwise "typo")}, {"Output"})
in CPower Query solution 5 for Convert Text To Number!, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
NumList = {
{"one", 1},
{"two", 2},
{"three", 3},
{"four", 4},
{"five", 5},
{"six", 6},
{"seven", 7},
{"eight", 8},
{"nine", 9},
{"ten", 10},
{"eleven", 11},
{"twelve", 12},
{"thirteen", 13},
{"fourteen", 14},
{"fifteen", 15},
{"sixteen", 16},
{"seventeen", 17},
{"eighteen", 18},
{"nineteen", 19},
{"twenty", 20},
{"thirty", 30},
{"forty", 40},
{"fifty", 50},
{"sixty", 60},
{"seventy", 70},
{"eighty", 80},
{"ninety", 90},
{"hundred", 100},
{"thousand", 1000},
{"million", 1000000},
{"billion", 1000000000}
},
Result = Table.TransformColumns(
Source,
{},
each [
a = Text.Lower(_),
b = Text.SplitAny(a, " -"),
c = List.ReplaceMatchingItems(b, NumList),
d = List.Split(c, 2),
e = List.Accumulate(
d,
0,
(start, curr) =>
start
+ (
if List.ContainsAny(curr, {100, 1000, 1000000, 1000000000}) then
List.Product(curr)
else
List.Sum(curr)
)
)
][e]
)
in
ResultPower Query solution 6 for Convert Text To Number!, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lower = Table.TransformColumns(Source, {{"Date", Text.Lower}}),
Lists = {
{"one", 1},
{"two", 2},
{"three", 3},
{"four", 4},
{"five", 5},
{"six", 6},
{"seven", 7},
{"eight", 8},
{"nine", 9},
{"ten", 10},
{"eleven", 11},
{"twelve", 12},
{"thirteen", 13},
{"fourteen", 14},
{"fifteen", 15},
{"sixteen", 16},
{"seventeen", 17},
{"eighteen", 18},
{"nineteen", 19},
{"twenty", 20},
{"thirty", 30},
{"forty", 40},
{"fifty", 50},
{"sixty", 60},
{"seventy", 70},
{"eighty", 80},
{"ninety", 90},
{"hundred", 100},
{"thousand", 1000},
{"million", 1000000}
},
Custom = Table.AddColumn(
Lower,
"Output",
each
let
a = List.ReplaceMatchingItems(Text.SplitAny([Date], " -"), Lists),
b = List.Split(a, 2),
c = List.Transform(
b,
(x) => if List.Sum(x) > 100 then x{0} * x{1} else try x{0} + x{1} otherwise x{0}
),
d = List.Sum(c)
in
d
),
Final = Table.SelectColumns(Custom, {"Output"})
in
FinalSolving the challenge of Convert Text To Number! with Excel
Excel solution 1 for Convert Text To Number!, proposed by Bo Rydobon 🇹🇭:
=LET(
one,
{"One",
"Two",
"Three",
"Four",
"Five",
"Six",
"Seven",
"Eight",
"Nine"},
sn,
SUBSTITUTE(
TAKE(
one,
,
-4
),
"t", ),
ten,
HSTACK(
"Ten",
"Eleven",
"Twelve",
"Thirteen",
"Fourteen",
"Fifteen",
sn&"teen"
),
ty,
HSTACK(
"Twenty",
"Thirty",
"Forty",
"Fifty",
sn&"ty"
),
oty,
HSTACK(
one,
ten,
ty
),
n,
HSTACK(
SEQUENCE(
,
20
),
SEQUENCE(
,
7,
3
)*10
),
m,
, re,
LAMBDA(
re,
tx,
o,
LET(
p,
@TEXTSPLIT(
tx,
{"-",
" "}
),
s,
IFNA(
o+XLOOKUP(
p,
oty,
n
),
o*XLOOKUP(
p,
{"hundred",
"thousand",
"million"},
10^{2,
3,
6}
)
),
x,
TEXTAFTER(
tx,
{"-",
" "}
),
IF(
ISNA(
x
),
s,
re(
re,
x,
s
)
)
)
),
MAP(
B3:B5,
LAMBDA(
a,
re(
re,
a,
)
)
)
)Excel solution 2 for Convert Text To Number!, proposed by Julian Poeltl:
=MAP(
B3:B5,
LAMBDA(
D,
LET(
F,
TEXTBEFORE(
D,
" "
),
M,
TEXTAFTER(
TEXTBEFORE(
D,
" ",
2
),
" "
),
E_WrittenOutEnglishNumber_into_NUMBER(
F
)*IF(
M="thousand",
1000,
100
)+E_WrittenOutEnglishNumber_into_NUMBER(
TEXTAFTER(
D,
" ",
2
)
)
)
)
)
=LAMBDA(
NumberasText,
LET(
TV,
TEXTBEFORE(
NumberasText,
"-",
,
,
,
NumberasText
),
TVN,
SWITCH(
LOWER(
TRIM(
TV
)
),
"zero",
0,
"one",
1,
"two",
2,
"three",
3,
"four",
4,
"five",
5,
"six",
6,
"seven",
7,
"eight",
8,
"nine",
9,
"ten",
10,
"eleven",
11,
"twelve",
12,
"thirteen",
13,
"fourteen",
14,
"fifteen",
15,
"sixteen",
16,
"seventeen",
17,
"eighteen",
18,
"nineteen",
19,
"twenty",
20,
"thirty",
30,
"forty",
40,
"fifty",
50,
"sixty",
60,
"seventy",
70,
"eighty",
80,
"ninety",
90,
"one hundred",
100
),
TN,
TEXTAFTER(
NumberasText,
"-",
,
,
,
""
),
TAN,
IFERROR(
SWITCH(
LOWER(
TRIM(
TN
)
),
"one",
1,
"two",
2,
"three",
3,
"four",
4,
"five",
5,
"six",
6,
"seven",
7,
"eight",
8,
"nine",
9
),
0
),
RES,
TVN+TAN,
RES
)
)Excel solution 3 for Convert Text To Number!, proposed by Fausto Bier:
=MAP(
B3:B5,LAMBDA(
n,REDUCE(
,XLOOKUP(
TEXTSPLIT(
n,,{" ";
"-"}
),VSTACK(
{"one";
"two";
"three";
"four";
"five";
"six";
"seven";
"eight";
"nine";
"ten";
"eleven";
"twelve"},{"thir";
"four";
"fif";
"six";
"seven";
"eigh";
"nine"}&"teen",{"twen";
"thir";
"for";
"fif";
"six";
"seven";
"eigh";
"nine"}&"ty",{"hundred";
"thousand"}
),VSTACK(
{1;
2;
3;
4;
5;
6;
7;
8;
9;
10;
11;
12;
13;
14;
15;
16;
17;
18;
19;
20;
30;
40;
50;
60;
70;
80;
90;
100},10^3
)
),LAMBDA(
a,v,IF(
v>99,a*v,a+v
)
)
)
)
)Excel solution 4 for Convert Text To Number!, proposed by Hamidi Hamid:
=TEXTSPLIT(
AI.ASK(
"translate into number",
B3:B5
),
,
","
)Excel solution 5 for Convert Text To Number!, proposed by Md. Zohurul Islam:
=LET(
a,
B3:B5, b,
AI.ASK(
"translate into number",
a
), c,
TOCOL(
--TEXTSPLIT(
b,
", "
)
), c
)Excel solution 6 for Convert Text To Number!, proposed by Thang Van:
=TEXTSPLIT(
AI.ASK(
"help me convert text to number"B3:B5
),
,
","
)Solving the challenge of Convert Text To Number! with Python
Python solution 1 for Convert Text To Number!, proposed by Konrad Gryczan, PhD:
import pandas as pd
from word2number import w2n
path = "CH-190 Convert Text to number.xlsx"
input = pd.read_excel(path, usecols="B", skiprows=1, nrows=4)
test = pd.read_excel(path, usecols="D", skiprows=1, nrows=4)
input['Output'] = input.pop('Date').apply(w2n.word_to_num)
print(input.equals(test)) # TrueSolving the challenge of Convert Text To Number! with R
R solution 1 for Convert Text To Number!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-190 Convert Text to number.xlsx"
input = read_excel(path, range = "B2:B5")
test = read_excel(path, range = "D2:D5")
library(reticulate)
w2n = import("word2number")$w2n
result = input %>%
mutate(Output = map_dbl(Date, ~w2n$word_to_num(.x))) %>%
select(Output)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUESolving the challenge of Convert Text To Number! with Google Sheets
Google Sheets solution 1 for Convert Text To Number!, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=746725081#gid=746725081