Very long Binary Number but Excel treats it as Decimal one

[saqibalikhan]

Hello everyone!
I am dealing with 23-bit long SRAM memory addresses. I copy the data from CSV file and split into column. all the data is in binary including one column containing 23-bit long binary number, for example, "10000000111100101010100". Excel treats this data as DECIMAL and represent it in scientific notation. Even if i change the number format, spit is using 'Right' or 'Left' functions, still it is represented as decimal number in scientific notation. I know, i can't convert this long number to decimal directly. here is what i want to achieve:

• i want Excel to consider the number as Binary NOT Decimal
• split first 6-bits in separate column and get the equivalent decimal #
• enlist the remaining 17-bit number into other column and to get its equivalent decimal

quick response would be appreciated greatly.

thanks
Saqib

 

[pgc01]

Hi

Excel will not consider your number binary. There's no such type in the worksheet.

What you can do is to import it from the CSV a Text. This way it will not convert the value to a number and you can then use the text functions to extract whatever you need.

To import easily the data in the format you want, change it extension to .txt. This will result in a dialog (like the Text to Columns dialog) when you import the file, where you can specify that you want that data as text.

If you need more help, post 2 or 3 rows of the CSV and what you need.

 

[saqibalikhan]

Hi,
I have simply imported the data as TXT file now. the representation of data is still scientific. here are 5 rows from the CSV file ( header line is also there):
[TABLE="width: 658"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Fault Num[/TD]
[TD]Read Cnt[/TD]
[TD]Fault Address[/TD]
[TD]Read Data[/TD]
[TD]Good Data[/TD]
[TD]Test Mode[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100011110101111000000[/TD]
[TD="align: right"]1010101010100010[/TD]
[TD="align: right"]1010101010101010[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100011110101111000000[/TD]
[TD="align: right"]101010101010101[/TD]
[TD="align: right"]1010101010101010[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1010101010101010[/TD]
[TD="align: right"]1010101010101010[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]100011110101111000000[/TD]
[TD="align: right"]1010101010100010[/TD]
[TD="align: right"]1010101010101010[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]100011110101111000000[/TD]
[TD="align: right"]101010101010101[/TD]
[TD="align: right"]1010101010101010[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


now, what i want to do is the split the 3rd column (fault address) in to 2 column having length of 6 bits (right most) and remaining in separate column. then, I want to convert these two column into their equivalent decimal number.

I hope you understand what i want

thank you

 

[pgc01]

Hi


You said that the column had 23 bit numbers, but you posted 21 bit numbers?

Assuming 21 bit numbers, like in the example, for the last 6 in decimal:

=BIN2DEC(RIGHT(C2,6))

for the next 6 to the left:

=BIN2DEC(MID(C2,10,6))

 

[saqibalikhan]

the length of that column is not fixed, it is either 21 or 23 bits. it looks easy to convert first 6-bits, but how about converting remaining 17-bits (or 15) as one decimal number?
I mean to say, for "100011110101111100000" 21-bit long number
first 6-bits : "100000" conversion can be performed, like you said, using =BIN2DEC(RIGHT(C2,6))
last 15 bits: "100011110101111" -----> its equivalent decimal
thank you so much for you quick reply

Saqib

 

[pgc01]

Hi Saqib

You should check the help:

The BIN2DEC function syntax has the following arguments (argument: A value that provides information to an action,  an event, a method, a property, a function, or a  procedure.):

 
[LIST]
[*][B]Number   Required. The binary number you want to  convert. Number cannot contain more than 10 characters (10 bits). The most  significant bit of number is the sign bit. The remaining 9 bits are magnitude  bits. Negative numbers are represented using two's-complement notation.[/B] 
[/LIST]

THis means that you cannot use BIN2DEC() directly with 15 digits.

Split it in 2:

=BIN2DEC(LEFT(A1,7))*256+BIN2DEC(RIGHT(A1,8))

 

[saqibalikhan]

Thank you so much, Sir!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[saqibalikhan]

Hi again,
i just tried to split 21-bit number using =RIGHT(C3,6), excel returns the same number (21 bit)
when i use =BIN2DEC(RIGHT(C3,6)), i get the error- maybe the number is too long

image for your reference
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

Saqib

 

[pgc01]

Hi

That makes no sense, unless you have it as a number value, which you said you don't have.

Try this test.

- select a cell and choose Text format
- enter in the formula bar for ex.: 100100011110101111000000100011 and press enter

Notice that it has 30 digits, but since I formatted the cell as text they appear as I entered them

Now with

=RIGHT(A1,3)

I'm sure you'll get "011".

Please do this test and see that it works. Then adapt it to your case.