# 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

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
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

##### New Member
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

##### MrExcel MVP
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
LMN
3009876543235895
 Sheet5

#### RonB1111

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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.

1,082,102
Messages
5,363,146
Members
400,720
Latest member
Pettel

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...