64 bit Binary

**Gimboidnk** · 08-13-2010, 06:34 PM

Hey Guys,

I've been referencing these forums for some time, but have always generally found what I needed here, or somewhere else if necessary, unfortunately though I've become stuck with this particular problem and can't find any way of doing what I need!

I receive a .csv with multiple columns of data. Column I contains a 64bit binary value stored in decimal format, the largest of which can be produced is:

9223372036854770000

Now, I've already discovered that excel's built in dec2bin() function is not powerful enough to convert a number this large, and whilst there are some user techniques out there to break down larger numbers, but it seems mine is too big for that too.

What I need to do is find a way to

1) Convert it back into Binary, (for the above one, for examples sake)
111111111111111111111111111111111111111111111111110100000000000

2) Split this output into separate columns, e.g. column I has the original decimal value, columns M onward would then be filled with 1,1,1,1,1 etc

I have no coding skill whatsoever, but I do understand how VB functions so I imagine this is possible via a custom function maybe, please help!

**Andrew-R** · 08-13-2010, 06:49 PM

Here you go ... this works from the activecell, but it's easily adaptable to work from other ranges, if you require.

Please Login or Register  to view this content.

**Gimboidnk** · 08-13-2010, 06:53 PM

You sir, are a gentleman and a scholar, I was hoping that someone somewhere might be able to help me with this, but a response as quick as yours, which has worked instantly and perfectly.

If I was female I'd offer to have your babys, THANK YOU!

**Andrew-R** · 08-13-2010, 06:56 PM

Glad to help. You're welcome to have my babies ... could you have them tomorrow night, while I go to the pub? Thanks.

**shg** · 08-14-2010, 01:31 PM

Andrew, I believe you're routine will have a problem with an input with more than 15 significant digits, e.g., 9223372036854769999.

Please Login or Register  to view this content.

Examples:

Please Login or Register  to view this content.

The commas are optional.

The formula in B2 and down is =IntStr2Str(A2, TRUE)

**alonglick** · 08-11-2014, 02:46 AM

Hi,
This is exactly a function i was looking for!
But how can i insert this nice code into excel and use it?
Thank you!
Alon (newee on the forum)

**Lukael** · 08-11-2014, 02:50 AM

Please Login or Register  to view this content.

Alt+F11, then insert a module and paste It into It.

**Pepe Le Mokko** · 08-11-2014, 02:58 AM

Originally Posted by alonglick

Hi,
This is exactly a function i was looking for!
But how can i insert this nice code into excel and use it?
Thank you!
Alon (newee on the forum)

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**qtrcoShawn** · 10-23-2014, 04:10 PM

post removed. will start a new thread.

**protonLeah** · 10-23-2014, 08:29 PM

qtrcoShawn,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.