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

 

[saqibalikhan]

Hi,
it works this way, but i got million of values to process. this method wont help.

thank you

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.

 

[pgc01]

Sorry, I don't understand.

If you import it as text, it does not matter whether you have 1 value or 1 million.

You said in post #3 that you have imported the file as TXT. In that case you should import the column Fault Address as text and everything will work.

Am I missing something?