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
Joined
Mar 15, 2013
Messages
35
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
Joined
Nov 28, 2011
Messages
2,277
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
Joined
May 26, 2009
Messages
16,666
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
Joined
Mar 15, 2013
Messages
35
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
Joined
Mar 15, 2013
Messages
35
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
Joined
May 26, 2009
Messages
16,666
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
Joined
Nov 28, 2011
Messages
2,277
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
Joined
Mar 15, 2013
Messages
35
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
Mar 15, 2013
Messages
35
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top