Convert the numbers in column B to the text like column H
📌 Challenge Details and Links
Challenge Number: 22
Challenge Difficulty: ⭐⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert Number To Text! with Power Query
Power Query solution 1 for Convert Number To Text!, proposed by Glyn Willis:
let
zero="Zero",
tens={ "","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"},
twenty={"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
mplier={ "","Trillion","Billion","Million","Thousand"},
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type number}}),Solving the challenge of Convert Number To Text! with Excel
Excel solution 1 for Convert Number To Text!, proposed by Bo Rydobon 🇹🇭:
=MAP(B3:B10,
LAMBDA(n,LET(l,
LAMBDA(No,
LET(p,
{14,
11,
8,
5,
2},
f,
FIXED(
No
),one,
{"",
"One",
"Two",
"Three",
"Four",
"Five",
"Six",
"Seven",
"Eight",
"Nine"},ten,
{"Ten",
"Eleven",
"Twelve",
"Thirteen",
"Fourteen",
"Fifteen",
"Sixteen",
"Seventeen",
"Eighteen",
"Nineteen"},ty,
{"";1;"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"},l,
IF(
SEQUENCE(
10
)=2,
ten,
ty&" "&one
),TRIM(IF(INT(
No
),
CONCAT(INDEX(
one,
MOD(
No/10^p,
10
)+1
)&IF(
INT(
MOD(
No/10^p,
10
)
),
" Hundred ",
""
)&
INDEX(l,
MOD(MOD(No/10^(p-1),
1000),
10)+1,
MOD(MOD(No/10^(p-2),
1000),
10)+1)&
IF(INT(MOD(No,
10^(p+1))/10^(p-2)),
{" Trillion ",
" Billion ",
" Million ",
" Thousand ",
""},
"")),
"Zero")&
IF(
-RIGHT(
f,
2
),
" point "&INDEX(
l,
LEFT(
RIGHT(
f,
2
)
)+1,
RIGHT(
f
)+1
),
""
)))),
l(
n
))))Excel solution 2 for Convert Number To Text!, proposed by Julian Poeltl:
=LET(N,
G3:G10,
AK,
IFERROR(
TEXTAFTER(
N-ROUNDDOWN(
N,
0
),
","
),
0
),
BK,
ROUNDDOWN(
N,
0
),
BKupH,
N-ROUNDDOWN(
BK,
-2
),
H,
(ROUNDDOWN(
N,
-2
)-ROUNDDONW(
BK,
-3
))/100,
T,
(ROUNDDONW(
N,
-3
)-ROUNDDONW(
N,
-4
))/1000,
BKLH,
IFS(
BKupH<20,
SWITCH(
BK,
0,
"Zero",
1,
"One",
2,
"Two",
3,
"Three",
4,
"Four",
5,
"Five",
6,
"Six",
7,
"Seven",
8,
"Eight",
9,
"Nine",
10,
"Ten",
11,
"Eleven",
12,
"Twelve",
13,
"Thirteen",
14,
"Fourteen",
15,
"Fifteen",
16,
"Sixteen",
17,
"Seventeen",
18,
"Eighteen",
19,
"Nineteen"
),
1,
SWITCH(
LEFT(
BKupH,
1
)*1,
2,
"Twenty",
3,
"Thirty",
4,
"Forty",
5,
"Fifty",
6,
"Sixty",
7,
"Seventy",
8,
"Eighty",
9,
"Ninety"
)&"-"&SWITCH(
RIGHT(
BKupH,
1
)*1,
1,
"One",
2,
"Two",
3,
"Three",
4,
"Four",
5,
"Five",
6,
"Six",
7,
"Seven",
8,
"Eight",
9,
"Nine"
)
),
HH,
IFERROR(
IFS(
H>0,
SWITCH(
H,
1,
"One",
2,
"Two",
3,
"Three",
4,
"Four",
5,
"Five",
6,
"Six",
7,
"Seven",
8,
"Eight",
9,
"Nine"
)
)&" "&"Hundred",
""
),
TT,
IFERROR(
IFS(
T>0,
SWITCH(
T,
1,
"One",
2,
"Two",
3,
"Three",
4,
"Four",
5,
"Five",
6,
"Six",
7,
"Seven",
8,
"Eight",
9,
"Nine"
)
)&" "&"Thousand",
""
),
BKT,
TRIM(
TT&" "&HH&" "&BKLH
)Excel solution 3 for Convert Number To Text!, proposed by Julian Poeltl:
=LET(N,
G3:G10,
AK,
IFERROR(
TEXTAFTER(
N-ROUNDDOWN(
N,
0
),
","
),
0
),
BK,
ROUNDDOWN(
N,
0
),
BKupH,
N-ROUNDDOWN(
BK,
-2
),
H,
(ROUNDDOWN(
N,
-2
)-ROUNDDOWN(
BK,
-3
))/100,
T,
(ROUNDDOWN(
N,
-3
)-ROUNDDOWN(
N,
-4
))/1000,
BKLH,
IFS(
BKupH<20,
NTwenty(
BK
),
1,
NHundred(
BKupH
)&"-"&NTwenty(
RIGHT(
BKupH,
1
)*1
)
),
HH,
IFERROR(
IFS(
H>0,
NTwenty(
H
)
)&" "&"hundred",
""
),
TT,
IFERROR(
IFS(
T>0,
NTwenty(
T
)
)&" "&"thousand",
""
),
BKT,
TRIM(
TT&" "&HH&" "&BKLH
),
AKT,
IFS(
AK*1<20,
NTwenty(
AK*1
),
1,
NHundred(
AK
)&"-"&NTwenty(
RIGHT(
AK,
1
)*1
)
),
ALSm,
LOWER(
IF(
AK=0,
BKT,
BKT&" point "&AKT
)
),
UPPER(
LEFT(
ALSm,
1
)
)&RIGHT(
ALSm,
LEN(
ALSm
)-1
)) NHundred:
=LAMBDA(
N?,
SWITCH(
LEFT(
N?,
1
)*1,
2,
"Twenty",
3,
"Thirty",
4,
"Forty",
5,
"Fifty",
6,
"Sixty",
7,
"Seventy",
8,
"Eighty",
9,
"Ninety"
)
) NTwenty:
=LAMBDA(
N?,
SWITCH(
N?,
0,
"Zero",
1,
"One",
2,
"Two",
3,
"Three",
4,
"Four",
5,
"Five",
6,
"Six",
7,
"Seven",
8,
"Eight",
9,
"Nine",
10,
"Ten",
11,
"Eleven",
12,
"Twelve",
13,
"Thirteen",
14,
"Fourteen",
15,
"Fifteen",
16,
"Sixteen",
17,
"Seventeen",
18,
"Eighteen",
19,
"Nineteen"
)
)Excel solution 4 for Convert Number To Text!, proposed by JvdV –:
=MAP(
B3:B10,
LAMBDA(
s,
LET(
t,
INDEX(
TEXTSPLIT(
WEBSERVICE(
"https://translate.googleapis.com/translate_a/single?client=gtx&sl=th&tl=en&dt=t&q="&ENCODEURL(
BAHTTEXT(
s
)
)
),
CHAR(
34
)
),
2
),
f,
REDUCE(
SUBSTITUTE(
REPT(
"Zero point ",
ISERR(
FIND(
"baht",
t
)
)
)&t,
" and ",
" point "
),
{" baht",
" only",
" satang",
"It's ",
"."},
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
)
)
),
UPPER(
LEFT(
f
)
)&LOWER(
MID(
f,
2,
LEN(
f
)
)
)
)
)
)
Solving the challenge of Convert Number To Text! with R
R solution 1 for Convert Number To Text!, proposed by Konrad Gryczan, PhD:
library called "english" which transform integers to its english names. But we had decimal numbers as well so some transformation was also needed. Here it is.
library(tidyverse)
library(readxl)
library(english)
input = read_excel("files/CH-022 Convert Number To Text.xlsx" , range = "B2:B10")
test = read_excel("files/CH-022 Convert Number To Text.xlsx" , range = "G2:H10")
result = input %>%
mutate(
number = as.character(Number),
integer_part = map(number, ~ strsplit(.x, "\.")[[1]][1]) %>% as.integer(),
decimal_part = map(number, ~ strsplit(.x, "\.")[[1]][2]) %>% as.integer()
) %>%
mutate(
integer_part_text = map_chr(integer_part, ~ as.character(as.english(.x))),
decimal_part_text = map_chr(decimal_part, ~ as.character(as.english(.x))),
Text = ifelse(is.na(decimal_part),
integer_part_text,
paste0(integer_part_text, " point ", decimal_part_text)) %>%
str_to_sentence() %>%
str_remove_all(" and")
) %>%
select(Number, Text)