Home » Convert to Binary Numbers

Convert to Binary Numbers

Convert the given positive numbers to Binary numbers. You may need to deploy an appropriate algorithm to do so. Hence, you may need to browse through internet to pick an algorithm for this.

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

Solving the challenge of Convert to Binary Numbers with Power Query

Power Query solution 1 for Convert to Binary Numbers, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  AddedBinary = Table.AddColumn(
    Source, 
    "Binary", 
    each Text.Combine(
      List.Transform(
        List.Reverse(
          List.Generate(
            () =>
              [Quotient = Number.IntegerDivide([Number], 2), Remainder = Number.Mod([Number], 2)], 
            each [Quotient] <> 0 or [Remainder] <> 0, 
            each [
              Quotient  = Number.IntegerDivide([Quotient], 2), 
              Remainder = Number.Mod([Quotient], 2)
            ], 
            each [Remainder]
          )
        ), 
        Text.From
      ), 
      ""
    )
  )[[Binary]]
in
  AddedBinary
Power Query solution 2 for Convert to Binary Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
let
  MyFun = (Number) =>
    let
      Generate = List.Generate(
        () => [x = Number, y = Number.Mod(x, 2)], 
        each [x] <> 0, 
        each [x = Number.IntegerDivide([x], 2), y = Number.Mod(x, 2)], 
        each Text.From([y])
      ), 
      Combine = Text.Combine(Generate), 
      Reverse = Text.Reverse(Combine)
    in
      Reverse, 
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(Source, "Binary", each MyFun([Number]))
in
  Return
Power Query solution 3 for Convert to Binary Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
let
  MyFun = (Number, Binary) =>
    if Number = 0 then
      Binary
    else
      @MyFun(Number.IntegerDivide(Number, 2), Text.From(Number.Mod(Number, 2)) & Binary), 
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(Source, "Binary", each MyFun([Number], ""))
in
  Return
Power Query solution 4 for Convert to Binary Numbers, proposed by Jaroslaw Kujawa: 
let 
 list_gen=List.Generate(()=0, each _ 21, each _+1, each  Number.RoundDown( numerek / Number.Power(2,_),0)),
 list_s=List.Sort( list_gen),
 list_t=List.Transform( list_s, each Number.Mod(_,2)),
 list_rem=List.RemoveFirstN( list_t, List.PositionOf(  list_t , 1))
in
 Lines.ToText( List.Transform( list_rem , Text.From),)
 Use to_bin here:let
 Source = Excel.CurrentWorkbook(){[Name=Table25]}[Content],
 hashtag#Changed Type = Table.TransformColumnTypes(Source,{{Number, Int64.Type}, {Binary, type text}}),
 hashtag#Added Custom = Table.AddColumn(hashtag#Changed Type, Custom, each to_bin([Number]))
in
 hashtag#Added Custom
Power Query solution 5 for Convert to Binary Numbers, proposed by Guillermo Arroyo: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla2"]}[Content], 
  Rename = Table.RenameColumns(Origen, {{"Number", "Binary"}}), 
  Bin = Table.TransformColumns(
    Rename, 
    {
      {
        "Binary", 
        each 
          let
            y = "", 
            NumtoBin = (n as number, m as text) as text =>
              if n = 0 then
                m
              else
                @NumtoBin(Number.IntegerDivide(n, 2), Number.ToText(Number.Mod(n, 2)) & m)
          in
            NumtoBin(_, y), 
        type text
      }
    }
  )
in
  Bin
Power Query solution 6 for Convert to Binary Numbers, proposed by Jan Willem Van Holst: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjRQitWJVjIxhVAWxmDa1BDCNzSASpiamVuAGZYwEUMjY5icARAoxcYCAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Number = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Number", Int64.Type}}), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "answer", 
    (x) =>
      let
        _bin = List.Generate(
          () => [
            num   = x[Number], 
            digit = Number.Mod(x[Number], 2), 
            div   = Number.RoundDown(x[Number] / 2)
          ], 
          each [num] > 0, 
          each [num = [div], digit = Number.Mod([div], 2), div = Number.RoundDown([div] / 2)], 
          each [digit]
        )
      in
        Text.Combine(List.Transform(List.Reverse(_bin), Text.From))
  )
in
  #"Added Custom"
Power Query solution 7 for Convert to Binary Numbers, proposed by Udit Chatterjee: 
let
 fxBinaryToDecimal = (decimalNum as number, storageList as list) =>
 let
 binaryBase = 2,
 intDivison = Number.IntegerDivide(decimalNum, binaryBase),
 remainder = {Number.Mod(decimalNum, binaryBase)},
 addToList = List.Combine({storageList, remainder}),
 recursionStep =
 if intDivison >= 1 then
 @fxBinaryToDecimal(intDivison, addToList)
 else
 Number.FromText(Text.Combine(List.Transform(List.Reverse(addToList), each Number.ToText(_))))
 in
 recursionStep,
 Source = xlChallenge153,
 addFunctionCol = Table.AddColumn(Source, "Binary", each fxBinaryToDecimal([Number], {}), Int64.Type)
in
 addFunctionCol


RECURSION ROCKS ⚡👩‍💻
Power Query solution 8 for Convert to Binary Numbers, proposed by Štěpán Rešl: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjRQitWJVjIxhVAWxmDa1BDCNzSASpiamVuAGZYwEUMjY5icARAoxcYCAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", Int64.Type}}), 
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type", 
    "BASE", 
    each 
      let
        input = [Column1]
      in
        (if input <= 1 then Text.From(input) else "1")
          & Text.Reverse(
            Text.Combine(
              List.Generate(
                () => [result = null, actual = input], 
                each [actual] >= 1, 
                each [
                  result = Text.From(Number.Mod([actual], 2)), 
                  actual = Number.RoundDown([actual] / 2)
                ], 
                each [result]
              )
            )
          )
  )
in
  #"Added Custom1"

Solving the challenge of Convert to Binary Numbers with Excel

Excel solution 1 for Convert to Binary Numbers, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A10,LAMBDA(a,LET(n,2,d,INT(LOG(a,n)+1),CONCAT(MOD(INT(a/n^(d-SEQUENCE(d))),n)))))
Excel solution 2 for Convert to Binary Numbers, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A10,LAMBDA(a,LET(n,2,d,INT(LOG(a,n)+1),CONCAT(MOD(RIGHT(QUOTIENT(a,n^(d-SEQUENCE(d))),2),n)))))

same as BASE(A2:A10,2)
Excel solution 3 for Convert to Binary Numbers, proposed by Rick Rothstein: 
=MAP(A2:A10,LAMBDA(r,LET(h,DEC2HEX(r),REDUCE("",MID(h,SEQUENCE(LEN(h)),1),LAMBDA(a,x,a&HEX2BIN(x,4))))))
Excel solution 4 for Convert to Binary Numbers, proposed by John V.: 
=MAP(A2:A10,LAMBDA(x,LET(s,SORT(SCAN(x,ROW(1:15),LAMBDA(i,x,INT(i/2)))),CONCAT(IF(s,MOD(s,2),""),MOD(x,2)))))

✅ Recursive:
=MAP(A2:A10,LAMBDA(x,LET(f,LAMBDA(g,n,b,IF(n,g(g,INT(n/2),CONCAT(MOD(n,2),b)),b)),f(f,x,))))

✅ Just with Excel function:
=BASE(A2:A10,2)
Excel solution 5 for Convert to Binary Numbers, proposed by Kris Jaganah: 
=MAP(A2:A10,LAMBDA(a,LET(b,VSTACK(a,SCAN(a,SEQUENCE(20),LAMBDA(x,y,QUOTIENT(x,2)))),c,MOD(b,2),d,SEQUENCE(ROWS(b)),e,XLOOKUP(0,b,d)-1,CONCAT(SORTBY(TAKE(c,e),TAKE(d,e),-1)))))
Excel solution 6 for Convert to Binary Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _d, A2:A10,
 _f, LAMBDA(ME, n, x, IF(n, ME(ME, INT(n / 2), MOD(n, 2) & x), x)),
 _r, MAP(_d, LAMBDA(a, _f(_f, a, ""))),
 _r
)
Excel solution 7 for Convert to Binary Numbers, proposed by Timothée BLIOT: 
=LET(A,A2:A10, F, LAMBDA(Me,i,o, IF(i=0, o, Me(Me, ((i-MOD(i,2))/2), CONCAT(o,MOD(i,2)) ))), MAP(A, LAMBDA(x, F(F,x,""))))
Excel solution 8 for Convert to Binary Numbers, proposed by Jaroslaw Kujawa: 
=MAP(GA2:GA10 , LAMBDA(a , LET(b , SEQUENCE(20 ,  , 0) , c , ROUNDDOWN(a/2^b , ) , d , MOD(c , 2) , e , SORT(HSTACK(c , d)) , f , DROP(DROP(e , XMATCH(1 , DROP(e ,  , 1))-1) ,  , 1) , CONCAT(f))))
Excel solution 9 for Convert to Binary Numbers, proposed by Guillermo Arroyo: 
=BASE(A2:A10,2)

=MAP(A2:A10,LAMBDA(n,LET(g,LAMBDA(a,b,c,IF(b=0,c,a(a,INT(b/2),CONCAT(MOD(b,2),c)))),g(g,n,""))))
Excel solution 10 for Convert to Binary Numbers, proposed by Mohamed Helmy: 
=MAP(A2:A10,LAMBDA(A,LET(
S,SORT(SCAN(A,SEQUENCE(100),LAMBDA(A,D,INT(A/2)))),
CONCAT(IF(S,MOD(RIGHT( S),2),""))&MOD(RIGHT( A),2))))
Excel solution 11 for Convert to Binary Numbers, proposed by Mohamed Helmy: 
=MAP(A2:A10,LAMBDA(A, LET(
S,SORT(SCAN(A,SEQUENCE(20),LAMBDA(A,D,
INT(A/2)))),CONCAT(IF(S,MOD(S,2),""))&MOD(A,2))))
Excel solution 12 for Convert to Binary Numbers, proposed by Rayan S.: 
=MAP(A2:A10, 
LAMBDA(a, LET(
x, ROUNDDOWN(SCAN(a, SEQUENCE(10000, , 2, 0), LAMBDA(a,b,a/b)), 0), 
s, VSTACK(a, x), 
TEXTJOIN("", , MOD(SORT(FILTER(s, s > 0), , 1), 2)))))
Excel solution 13 for Convert to Binary Numbers, proposed by Viswanathan M B: 
=LAMBDA(n, IF(n=1,"1", GetBinary(QUOTIENT(n,2))&MOD(n,2)))
Excel solution 14 for Convert to Binary Numbers, proposed by Gabriel Raigosa: 
=APILARV({"Number","Binary"},LET(n,A2:A10,APILARH(n,TEXTO(BASE(n,2),"#")))) 

ENG:   =VSTACK({"Number","Binary"},LET(n,A2:A10,HSTACK(n,TEXT(BASE(n,2),"#"))))

Solving the challenge of Convert to Binary Numbers with Python in Excel

Python in Excel solution 1 for Convert to Binary Numbers, proposed by Alejandro Campos: 
Code in cell D1
decimal_numbers = xl("A2:A10")[0]
binary_numbers_w_prefix = [bin(number) for number in decimal_numbers]
binary_numbers_Wno_prefix = [bin(number)[2:] for number in decimal_numbers]
Code in cell D2
binary_numbers_w_prefix
Code in cell F2
binary_numbers_Wno_prefix

&&&

Leave a Reply