How can I make it so that a cell is formatted to the point where whatever number I type in stays the exact way that I typed it

[popcornrulez]

Pretty much I just need to be able to have zeroes in front of numbers without converting the number to text. And also note that the numbers that I am going to be pasting in that cell need to be able to be any size and still be able to keep the zeroes in front of them.

popcornrulez

 

[Rick Rothstein]

Right now I'm just in the testing phase, so I'm currently just typing hexadecimal code into the cell. But in the future, I would like to be able to either copy the hex data from a hex editor and then paste it into the cell, or figure out a way to import the hex data directly from the file into the cell. I'm trying to make a spreadsheet that compresses files. Since every file is different, I need a way to have the data that I paste/import into the cell to stay exactly the same as the hex data from the original file.

I think it may be easier to handle the data if we develop some VBA code to do the importing, fixing things as necessary before delivering the data to the worksheet, as opposed to jury-rigging some method to handle type-ins that more than likely would not be applicable to the file import process. Do the files you want to eventually import currently exist? If so, and assuming there is some "standardization" as to the way the files are constructed, can you give us a description of the format/layout of those files? Or, better yet, can you upload one of them to your skydrive (if you have one) or to one of the free file-sharing websites on the internet (such as this one, http://www.box.net/files)?

 

[Rick Rothstein]

With this method when I type more than 10 digits with the apostrophe in front of the number it gives me the #NUM! error in the cells that have formulas that include that cell's number.

In addition to my questions in the last message I posted, I would also like to know what "calculations" you are doing with these hex numbers that your formulas are failing?

 

[popcornrulez]

I think it may be easier to handle the data if we develop some VBA code to do the importing, fixing things as necessary before delivering the data to the worksheet, as opposed to jury-rigging some method to handle type-ins that more than likely would not be applicable to the file import process. Do the files you want to eventually import currently exist? If so, and assuming there is some "standardization" as to the way the files are constructed, can you give us a description of the format/layout of those files? Or, better yet, can you upload one of them to your skydrive (if you have one) or to one of the free file-sharing websites on the internet (such as this one, http://www.box.net/files)?

When I get this spreadsheet perfected, I want to be able to import any file possible. Which means there's not really a specific file-type that I am going to be using. For now though I might just start by using ZIP Archives, I'll make a few then put them in my dropbox. My idea for the compression formulas is like this:

1. Convert Hexadecimal Data to Decimal
2. Divide Decimal Data by a number that produces no decimal points
3. Convert Decimal Data back to Hex
4. Add a 16-bit number to the beginning of the compressed Hex Data, (this number will be the amount of zeroes in front of the original Hex Data, i.e. if there was 10 zeroes at the front, it would look like "000000000000000A" without quotes.

That would be the process to compress the data. I want to get this finished before I worry about un-compressing the data.

 

[popcornrulez]

here are the links to the archives that I just made:

https://www.dropbox.com/s/v9eh2148c47wv1w/Archive%201.zip

https://www.dropbox.com/s/yznxklxd2gghsr9/Archive 2.zip

https://www.dropbox.com/s/6xnxhn67keb19u7/Archive 3.zip

Please note that when and if I get this perfected, I will be working with much bigger files.

 

[Rick Rothstein]

1. Convert Hexadecimal Data to Decimal
2. Divide Decimal Data by a number that produces no decimal points
3. Convert Decimal Data back to Hex

Does #2 derive from #1? By that I mean, is will your Hex Data convert to floating point Decimal values? If so, this may be a breaking point for your intentions as rounding will be involved as most (read almost all) decimal values do not have an exact hex equivalent (think of 0.666667 being used or represent 2/3... it will never be exact no matter how many 6's you place in front of that 7).

 

[popcornrulez]

Does #2 derive from #1? By that I mean, is will your Hex Data convert to floating point Decimal values? If so, this may be a breaking point for your intentions as rounding will be involved as most (read almost all) decimal values do not have an exact hex equivalent (think of 0.666667 being used or represent 2/3... it will never be exact no matter how many 6's you place in front of that 7).

https://www.dropbox.com/s/daqvp0uar9h3xax/Compression Spreadsheet.xlsx

Here's the link for my current spreadsheet, the way that I have it is so that you can look at the results of dividing by several different numbers, and then pick on that doesn't have decimal points.

Edit: My current method for the 16-bit number needs to be changed, because as of right now, it just counts how many characters are in the hex data, not how many zeroes are in front of it.

 

[Rick Rothstein]

Edit: My current method for the 16-bit number needs to be changed, because as of right now, it just counts how many characters are in the hex data, not how many zeroes are in front of it.

Give this formula a try (adjust all five cell references as needed)...

=LEN(B1)-LEN(MID(B1,FIND(LEFT(TRIM(SUBSTITUTE(B1,0," "))),B1),LEN(B1)))

 

[popcornrulez]

Give this formula a try (adjust all five cell references as needed)...

=LEN(B1)-LEN(MID(B1,FIND(LEFT(TRIM(SUBSTITUTE(B1,0," "))),B1),LEN(B1)))

Thanks that worked for the 16-bit string. How would I go about importing the hex data from a file into excel?

 

[popcornrulez]

bump.

 

[popcornrulez]

Can someone please help me make a vba script that imports a file's hexadecimal data into a cell?.....