0

I have this number 

111100000000000010001000

I want to extend it to 32 bits with leading zeros. In other words:

00000000111100000000000010001000

So I found this suggestion here: Add leading zeroes/0's to existing Excel values to certain length

is to use the Right function. So I do:

=RIGHT("00000000000000000000000000000000"+A1,32)

I end up getting a number in Engineering notation. So as suggested somewhere else I add:

=TEXT(RIGHT("00000000000000000000000000000000"+A1,32), "0")

I still get

111100000000000000000000

Not 32-bit and the trailing 10001000 has become zeros.

Any idea what's happening here??

Community
  • 1
  • 1
M S
  • 3
  • 3

1 Answers1

0

Excel takes that as a decimal number, not a binary number.

111100000000000010001000 as a decimal number is too much for the number precision Excel has to offer, so that is rounded to 111100000000000000000000 before you apply your zeros (which you can see yourself if you apply a numeric format to A1 that disallows scientific notation).

The solution is the same, treat all numbers as string. Prefix the source number in A1 with an apostrophe to make it a string, the RIGHT will then work as you expect.
Well, it actually won't, because I used + when I should have used &, so Excel will try to convert to numbers and actually make the addition. So correct the formula: 

=RIGHT("00000000000000000000000000000000"&A1,32)
Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Sorry but that didn't work. Have you tried it yourself? Update: Apologies, that actually did work. Adding the & was all that was needed. – M S Feb 26 '14 at 16:24