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

 

[RonB1111]

Home - Format - Format Cells - Number tab - Custom - then in the box directly underneath "Type" enter 00000 (or as many 0's as you want displayed) -OK

 

[JoeMo]

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

One way is to enter the number with an apostrophe as a prefix: example '009876.
Although the number will look like text (left aligned unless you change the alignment), it can still be used as a number. Example: cell A1 contains '009876. In B1 enter: =SQRT(A1/2) --> returns 70.27 which is the square root of 4938 (9876/2).

 

[popcornrulez]

One way is to enter the number with an apostrophe as a prefix: example '009876.
Although the number will look like text (left aligned unless you change the alignment), it can still be used as a number. Example: cell A1 contains '009876. In B1 enter: =SQRT(A1/2) --> returns 70.27 which is the square root of 4938 (9876/2).

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.

 

[popcornrulez]

Home - Format - Format Cells - Number tab - Custom - then in the box directly underneath "Type" enter 00000 (or as many 0's as you want displayed) -OK

This method would normally be fine with me if I was working with small numbers, but on occasion I will be working numbers that are big enough to the point where I don't want to use this method because I would have to count the amount of digits in the big number.

 

[JoeMo]

This method would normally be fine with me if I was working with small numbers, but on occasion I will be working numbers that are big enough to the point where I don't want to use this method because I would have to count the amount of digits in the big number.

Works ok for me using 15 digits:

Excel Workbook
	L	M	N
3	009876543235895	15	2222222.225
Sheet5

 

[RonB1111]

This method would normally be fine with me if I was working with small numbers, but on occasion I will be working numbers that are big enough to the point where I don't want to use this method because I would have to count the amount of digits in the big number.

You only enter the 000000... one time in the Format Cells box. When you enter a number you do not enter the preceding 0's (ie: 000000789 would be entered as 789, and excel would display it in the cell as 000000789.

It might help us if you described the nature of your project, whether the data is being manually entered or from an imported datasource, the nature of formula's your using, and the results you want.

 

[popcornrulez]

You only enter the 000000... one time in the Format Cells box. When you enter a number you do not enter the preceding 0's (ie: 000000789 would be entered as 789, and excel would display it in the cell as 000000789.

It might help us if you described the nature of your project, whether the data is being manually entered or from an imported datasource, the nature of formula's your using, and the results you want.

My Project involves taking hexadecimal code from files and putting it into a cell. And with some file-types, there will be a bunch of zeroes in front of the hex data.

 

[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.

My Project involves taking hexadecimal code from files and putting it into a cell. And with some file-types, there will be a bunch of zeroes in front of the hex data.

Can you clarify exactly what you are doing for us... the two text snippets I highlighted in red were taken from two of your posts and appear to conflict with each other.

 

[popcornrulez]

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.