Extract Numbers and Sum Digits

Extract the numeric digits from the given strings in a column and sum the digits for those extracted strings in another column.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 1
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Extract Numbers and Sum Digits with Power Query

Power Query solution 1 for Extract Numbers and Sum Digits, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Digits = Table.AddColumn(Source, "Digits", each Text.Select(Text.From([String]), {"0" .. "9"})), 
  Sum = Table.AddColumn(
    Digits, 
    "Sum", 
    each List.Sum(List.Transform(Text.ToList([Digits]), each Number.From(_)))
  )
in
  Sum

Power Query solution 2 for Extract Numbers and Sum Digits, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"String", type text}}), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Numbers", 
    each Text.Select([String], {"0" .. "9"})
  ), 
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.ToList([Numbers])), 
  #"Extracted Values" = Table.TransformColumns(
    #"Added Custom1", 
    {"Custom", each List.Sum(List.Transform(_, Number.From)), type number}
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Values", {{"Numbers", Int64.Type}}), 
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type1", {{"Custom", "Sum of Digits"}})
in
  #"Renamed Columns"

Power Query solution 3 for Extract Numbers and Sum Digits, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"String", type text}}), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Digit", 
    each Text.Select([String], {"0" .. "9"})
  ), 
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "SumDigit", each Text.ToList([Digit])), 
  #"Expanded SumDigit" = Table.ExpandListColumn(#"Added Custom1", "SumDigit"), 
  #"Changed Type1" = Table.TransformColumnTypes(#"Expanded SumDigit", {{"SumDigit", Int64.Type}}), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type1", 
    {"String", "Digit"}, 
    {{"SumDigit", each List.Sum([SumDigit]), type nullable number}}
  )
in
  #"Grouped Rows"

Power Query solution 4 for Extract Numbers and Sum Digits, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each try Text.ToList([String]) otherwise {[String]}
  ), 
  #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), 
  replaceerror = Table.TransformColumns(
    #"Expanded Custom", 
    {{"Custom", each try Number.From(_) otherwise "?"}}
  ), 
  #"Filtered Rows" = Table.SelectRows(replaceerror, each ([Custom] <> null)), 
  #"Grouped Rows" = Table.Group(
    #"Filtered Rows", 
    {"String"}, 
    {{"Digits", each Text.Combine(List.Transform(_[Custom], Text.From))}}
  ), 
  #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows", "?", "", Replacer.ReplaceText, {"Digits"}), 
  #"Added Custom1" = Table.AddColumn(
    #"Replaced Value", 
    "Sum of Digits", 
    each List.Sum(List.Transform(Text.ToList([Digits]), Number.From))
  ), 
  #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1", {{"Digits", Int64.Type}})
in
  #"Changed Type"

Power Query solution 5 for Extract Numbers and Sum Digits, proposed by Sergei Baklan:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  sumDigits = Table.AddColumn(
    Source, 
    "sum", 
    each List.Sum(
      List.Transform(List.Intersect({Text.ToList([String]), {"0" .. "9"}}), Number.FromText)
    )
  )
in
  sumDigits

Power Query solution 6 for Extract Numbers and Sum Digits, proposed by Udit Chatterjee:

let
  Source = #"PQChallenge-01", 
  // convert text to list and replace non-digit items by "_" 
  extractDigits = Table.AddColumn(
    Source, 
    "Digits", 
    each List.Transform(Text.ToList([String]), each try Number.FromText(_) otherwise "_")
  ), 
  // combine list items and replace "_" with none and change the datatype of digits to number 
  combineListItems = Table.TransformColumns(
    extractDigits, 
    {"Digits", each Text.Combine(List.Transform(_, Text.From)), type text}
  ), 
  replaceTextwithNone = Table.ReplaceValue(
    combineListItems, 
    "_", 
    "", 
    Replacer.ReplaceText, 
    {"Digits"}
  ), 
  datatypeChange = Table.TransformColumnTypes(replaceTextwithNone, {{"Digits", Int64.Type}}), 
  // get sum of the digits 
  getSumofDigits = Table.AddColumn(
    datatypeChange, 
    "Sum of Digits", 
    each try
      List.Sum(List.Transform(Text.ToList(Number.ToText([Digits])), each Number.FromText(_)))
    otherwise
      null, 
    Int64.Type
  )
in
  getSumofDigits

Power Query solution 7 for Extract Numbers and Sum Digits, proposed by Abdoul Karim N.:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ChangedType = Table.TransformColumnTypes(Source, {{"String", type text}}), 
  GetDigits = Table.AddColumn(ChangedType, "Digits", each Text.Select([String], {"0" .. "9"})), 
  Summing = Table.AddColumn(
    GetDigits, 
    "Sum of Digits", 
    each List.Sum(List.Transform(Text.ToList([Digits]), each Number.From(_)))
  )
in
  Summing

Power Query solution 8 for Extract Numbers and Sum Digits, proposed by Hakeem Lawrence:

let
 Source = Excel.Workbook(File.Contents("C:Usershakeem.lawrenceDownloadsPQ_Challenge_1_Problem.xlsx"), null, true),
 Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
 #"Removed Columns" = Table.RemoveColumns(Sheet1_Sheet,{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}), //Remove unused columns
 #"Removed Rows" = Table.Skip(#"Removed Columns",2), // drop first two empty rows
 #"Convert to Text" = Table.TransformColumnTypes(#"Removed Rows",{{"Column1", type text}}), // Convert column to type text
 #"Extract Numbers" = Table.AddColumn(#"Convert to Text", "Numbers", each Text.Select([Column1], {"0".."9"})), // extract numbers from cells
 #"Answer" = Table.AddColumn(#"Extract Numbers", "Sum of Digits", each List.Sum(List.Transform((Text.ToList([Numbers])), Number.FromText))) // sum of digits
in
 Answer

Solving the challenge of Extract Numbers and Sum Digits with Excel

Excel solution 1 for Extract Numbers and Sum Digits, proposed by محمد حلمي:

=REDUCE(
{"String","Digits","Sum of Digits"},B3:B11,
LAMBDA(c,b,LET(a,
IFERROR(MID(b,SEQUENCE(,LEN(b)),1)+0,""),
VSTACK(c,HSTACK(b,TEXTJOIN("",,a),SUM(a))))))

Excel solution 2 for Extract Numbers and Sum Digits, proposed by محمد حلمي:

=TEXTJOIN(;;IFERROR(MID(D3;ROW($1:$50);1)+0;""))+0
F3:=SUM(IFERROR(MID(D3;ROW($1:$50);1)+0;))

Excel solution 3 for Extract Numbers and Sum Digits, proposed by محمد حلمي:

=TEXTJOIN(;;IFERROR(MID(I3;ROW($1:$50);1)+0;""))+0
for sum
=SUM(IFERROR(MID(I3;ROW($1:$50);1)+0;)

Excel solution 4 for Extract Numbers and Sum Digits, proposed by 🇰🇷 Taeyong Shin:

=LET(r,REGEXREPLACE(B3:B11,"(d+)|.","$1"),HSTACK(r,MAP(r,LAMBDA(x,SUM(--(0®EXEXTRACT(x,".|$",1)))))))

Excel solution 5 for Extract Numbers and Sum Digits, proposed by Hussein SATOUR:

=SUM (IFERROR (MID(A2, SEQUENCE (LEN(A2)),1)*1, 0))

Excel solution 6 for Extract Numbers and Sum Digits, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=sharing&ouid=118330001425622029568&rtpof=true&sd=true

for digits, the excel formulae is;
=IFERROR(VALUE(MID($A2;LEN($A2);ROW(A1)));"")&TEXTJOIN(;;IFERROR(VALUE(IFERROR(MID($A2;LEN($A2)-ROW($A$1:$A$100);ROW($A$1));""));""))

for the sum of the digits, the excel formulae is;
=SUM(IFERROR(VALUE(MID($A2;LEN($A2);ROW(A1)));0);IFERROR(VALUE(IFERROR(MID($A2;LEN($A2)-ROW($A$1:$A$100);ROW($A$1));""));0))

Excel solution 7 for Extract Numbers and Sum Digits, proposed by Amardeep Singh:

=LET(r,
TEXTSPLIT(TEXTJOIN("#",,
MAP(B3:B11,LAMBDA(x,
LET(d,MID(x,SEQUENCE(LEN(x)),1),
digits,IFERROR((TOCOL(--d,3)),""),
sum_d,IFERROR(SUM(digits),""),
CONCAT(digits)&"-"&sum_d
)))),"-","#"),
HSTACK(CHOOSECOLS(r,1),IFERROR(--CHOOSECOLS(r,2),"")))

_____
(2) Second solution:

=DROP(REDUCE("",B3:B11,LAMBDA(a,v,
LET(d,--MID(v,SEQUENCE(LEN(v)),1),
digits,IFERROR(--d,""),
sum_d,SUM(digits),
VSTACK(a,HSTACK(CONCAT(digits),IF(sum_d,sum_d,"")))))),1)

Excel solution 8 for Extract Numbers and Sum Digits, proposed by Viswanathan M B:

=LET(x,VALUE(MID(Table1[Tbl],SEQUENCE(1, MAX(LEN(Table1[Tbl]))),1)),
 Nums, IF(ISNUMBER(x),x,""),
 Txt, BYROW(Nums, LAMBDA(a, VALUE(CONCAT(a)))),
 Val, BYROW(Nums, LAMBDA(a, SUM(a))),
 HSTACK(Txt, Val))

Excel solution 9 for Extract Numbers and Sum Digits, proposed by Juliano Santos Lima:

=SUM(IFERROR(VALUE(MID(F3,SEQUENCE(LEN(F3)),1)),0))

Excel solution 10 for Extract Numbers and Sum Digits, proposed by Muhammad Nauman:

=TEXTJOIN("",TRUE,IFERROR(1*MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),""))

=SUMPRODUCT(IFERROR(1*MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),0))

Solving the challenge of Extract Numbers and Sum Digits with Power Query

Solving the challenge of Extract Numbers and Sum Digits with Excel

Leave a Reply Cancel reply