Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Decimal to binary conversion for large numbers in Excel

I have some large numbers in an Excel sheet and I want to convert them to binary.

e.g.

12345678  
965321458  
-12457896
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

If we are talking positive number between 0 and 2^32-1 you can use this formula:

=DEC2BIN(MOD(QUOTIENT($A$1,256^3),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^2),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^1),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^0),256),8)

NOTE: =DEC2BIN() function cannot handle numbers larger than 511 so as you see my formula breaks your number into four 8-bit chunks, converts them to binary format and then concatenates the results.

Well, theoretically you can extend this formula up to six 8-bit chunks. Maximum precision you can get in Excel is 15 (fifteen) decimal digits. When exceeded, only the most significant 15 digits remain, the rest is rounded. I.e. if you type 12345678901234567 Excel will store it as 12345678901234500. So since 2^48-1 is 15 decimal digits long the number won't get rounded.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...