Binary to Hexadecimal

[Thiyagu8423]

I have 26byte number in one cell , can I convert it to Hexadecimal using formula in one shot

 

[Eric W]

First, I assume that the cell is formatted as Text, otherwise the 104 digits of 26 bytes would try to convert to an exponential number. Next, are you still using Excel 2013? If so, I can't think of a way to get the formula in a single cell (except for a UDF). In Excel 2016, Microsoft added several base conversion functions. And with the array functions, it got even easier.

Consider:

Book1
	A	B	C	D	E	F
1	Binary	Hexadecimal			Binary	Hex
2	11100101011110001110001011101110001110110101000001010101010101000011100011110101010101010101110001110111	E578E2EE3B50555438F5555C77			0000	0
3					0001	1
4		E			0010	2
5		E5			0011	3
6		E57			0100	4
7		E578			0101	5
8		E578E			0110	6
9		E578E2			0111	7
10		E578E2E			1000	8
11		E578E2EE			1001	9
12		E578E2EE3			1010	A
13		E578E2EE3B			1011	B
14		E578E2EE3B5			1100	C
15		E578E2EE3B50			1101	D
16		E578E2EE3B505			1110	E
17		E578E2EE3B5055			1111	F
18		E578E2EE3B50555
19		E578E2EE3B505554
20		E578E2EE3B5055543
21		E578E2EE3B50555438
22		E578E2EE3B50555438F
23		E578E2EE3B50555438F5
24		E578E2EE3B50555438F55
25		E578E2EE3B50555438F555
26		E578E2EE3B50555438F5555
27		E578E2EE3B50555438F5555C
28		E578E2EE3B50555438F5555C7
29		E578E2EE3B50555438F5555C77
Sheet5

Cell Formulas
Range	Formula
B2	B2	=CONCAT(BIN2HEX(MID(A2,SEQUENCE(LEN(A2)/4,,,4),4)))
B4:B29	B4	=B3&VLOOKUP(MID(A$2,ROWS(A$4:A4)*4-3,4),$E$2:$F$17,2,0)

The B2 formula is a single cell formula, but it requires the newer functions. For 2013, the best I could do was the series of functions in B4:B29, with B29 being the final answer. You could put the intermediate formulas horizontally in hidden columns, and only show the final result. Or if you're willing to use VBA, I could write a UDF (User-Defined Function) that would work in 2013 and only require a single cell.

 

[Domenic]

Maybe...

=CONCAT(BIN2HEX(MID(A2,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A2)/4))*4-4+1,4)))

Hope this helps!

 

[Thiyagu8423]

Maybe...

=CONCAT(BIN2HEX(MID(A2,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A2)/4))*4-4+1,4)))

Hope this helps!

Appreciate for help...will try tomorrow

 

[Thiyagu8423]

Thank you sir..Appreciate for help...will try tomorrow