Converting 3 cells to binary and grouping them into 8-bits then converting each 8-bit group back to decimal

[xjpx]

Hi all, let me go straight down to the question

As per the title, I want to convert 3 decimal values to 3 separate binary and group them into 8-bits groups then converting each group back to decimal.

Steps are as follows,
1. Take the data from sheet 1 K2, L2, M2 and N2 (Eg. K2: 34801152, L2: 196879 M2: ...)
2. Convert them to 32-bit binary (Eg. K2: 00000010000100110000011000000000, L2: 00000000000000110000000100001111)
3. Group the 32-bits into 8-bits groups (Eg. K2: 00000010 00010011 00000110 00000000, L2: 00000000 00000011 00000001 00001111)
4. Convert each 8-bit group into decimal (Eg. K2: 2.19.6.0 , L2: 0.3.1.15)
5. Paste these values into sheet 2 cells A2, B2, C2 and D2 respectively.

The end product should look like the image below in sheet 2.

Thanks all! If you require any more information please let me know

 

[BSALV]

in 4 steps (can be done in an UDF)

xxxxxxxx.xlsx
	A	B	C	D
1			consecutive divide by 256	modus 256
2	34801152		34.801.152	0
3	2.19.6.0		135.942	6
4	256		531	19
5			2	2
6			0	2.19.6.0
Blad1

Cell Formulas
Range	Formula
C2	C2	=+A2
D2:D5	D2	=MOD(C2+1,$A$4)-1
C3:C6	C3	=+TRUNC(C2/$A$4)
D6	D6	=TEXTJOIN(".",1,D5,D4,D3,D2)

 

[BSALV]

Function Bit8(getal)
     Application.Volatile

     If getal < 0 Then Exit Function
     Do
          rest = ((getal + 1) Mod 256) - 1
          Bit8 = "." & rest & Bit8
          getal = Int(getal / 256)
     Loop While getal > 0
     Bit8 = Mid(Bit8, 2)

End Function

 

[BSALV]

Function Bit(getal As Long)
     Application.Volatile
     If getal < 0 Then Exit Function
     Do
          rest = ((getal + 1) Mod 256) - 1
          Bit = "." & rest & Bit
          getal = Int(getal / 256)
     Loop While getal > 0
     Bit = Mid(Bit, 2)

End Function

xxxxxxxx.xlsx
	G	H	I	J	K	L	M
1	max	2.147.483.648	number	1.918.253.154	1.311.707.594	261.902.296	855.385.222
2			translated	114.86.56.98	78.47.17.202	15.156.79.216	50.252.36.134
3
Blad1

Cell Formulas
Range	Formula
H1	H1	=POWER(2,31)
J1:M1	J1	=RANDBETWEEN(1,$H$1)
J2:M2	J2	=BIT(J1)

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C								K	L
1
2	2.19.6.0	0.3.1.15									34801152	196879
Main

Cell Formulas
Range	Formula
A2:B2	A2	=TEXTJOIN(".",,BIN2DEC(MID(BASE(K2,2,32),SEQUENCE(,4,,8),8)))

 

[xjpx]

Another option

+Fluff 1.xlsm
	A	B	C								K	L
1
2	2.19.6.0	0.3.1.15									34801152	196879
Main

Cell Formulas
Range	Formula
A2:B2	A2	=TEXTJOIN(".",,BIN2DEC(MID(BASE(K2,2,32),SEQUENCE(,4,,8),8)))

This is great but I have to use the function ='Sheet 1'!K2*1 to extract the data from Sheet1 to Sheet 2. Other than that this works well.

 

[xjpx]

Function Bit(getal As Long)
     Application.Volatile
     If getal < 0 Then Exit Function
     Do
          rest = ((getal + 1) Mod 256) - 1
          Bit = "." & rest & Bit
          getal = Int(getal / 256)
     Loop While getal > 0
     Bit = Mid(Bit, 2)

End Function

xxxxxxxx.xlsx
	G	H	I	J	K	L	M
1	max	2.147.483.648	number	1.918.253.154	1.311.707.594	261.902.296	855.385.222
2			translated	114.86.56.98	78.47.17.202	15.156.79.216	50.252.36.134
3
Blad1

Cell Formulas
Range	Formula
H1	H1	=POWER(2,31)
J1:M1	J1	=RANDBETWEEN(1,$H$1)
J2:M2	J2	=BIT(J1)

This is great! I don't really get why you added a power and randbetween function. I used the VBA code and use =Bit(cell) function and it works.

 

[BSALV]

that was only for demonstration, to pick every time 4 new variables (the randbetween-formula).
2^31 = approx 2,150,000,000 = maximum number excel allows

 

[Fluff]

This is great but I have to use the function ='Sheet 1'!K2*1 to extract the data from Sheet1 to Sheet 2.

You never mentioned that the sells were on different sheets.

Glad to help & thanks for the feedback.