Real leading zeroes ...

[paquirl]

We have part numbers that only use 0-9 characters.
We need to make the column of part numbers meet the following requirements:
Each part number must be 11 digits long; if not 11 digits, leading zero is added until it becomes 11 digits.
Leading zeroes must be real, so no custom formatting technique.
After dealing with the above requirement, the column of part numbers must become formatted as text.
Would like to have intermediate excel users be able to achieve this quickly and efficiently without VBA.

 

[Joe4]

With VBA, you could automatically "pad" the entries with leading zeroes so that they are exactly 11 characters long (the users would need to run anything - this could be automtic).

Without VBA, you cannot do anything automatically.
So I think your best bet would be to pre-format the column as text, and then use Data Validation on those cells to make sure that they can only enter in entries of exactly 11 characters.

 

[paquirl]

With VBA, you could automatically "pad" the entries with leading zeroes so that they are exactly 11 characters long (the users would need to run anything - this could be automtic).

Without VBA, you cannot do anything automatically.
So I think your best bet would be to pre-format the column as text, and then use Data Validation on those cells to make sure that they can only enter in entries of exactly 11 characters.

I could have users follow a procedure of applying formulas. I know of a formula for making the cells text, but i'm not aware of one to take care of the "real" leading zeroes ... do you?

 

[Joe4]

I know of a formula for making the cells text

You do? You cannot control the format of the cell with an Excel formula that I know of.
Unless you mean taking the entry in another cell and applying the Text function to it, i.e.

=TEXT(A1,"00000000000")

but that is not affecting the format of that cell, it is controlling what it is returning.
And that involves having to use two cells instead of one.

but i'm not aware of one to take care of the "real" leading zeroes ... do you

Yes, but only with VBA.
What is your hesitancy with using a VBA solution?
The "users" don't need to know anything about VBA, nor do they have to do anything extra. It can be triggered to run automatically upon them entered data into the cell.

With Data Validation, you can require that their entry is exactly 11 characters long, and not accept it if it isn't.
And you can even return a custom message like "You must pad your entry with leading zeroes so that it is exactly 11 characters long".

 

[paquirl]

You do? You cannot control the format of the cell with an Excel formula that I know of.
Unless you mean taking the entry in another cell and applying the Text function to it, i.e.

=TEXT(A1,"00000000000")

but that is not affecting the format of that cell, it is controlling what it is returning.
And that involves having to use two cells instead of one.


Yes, but only with VBA.
What is your hesitancy with using a VBA solution?
The "users" don't need to know anything about VBA, nor do they have to do anything extra. It can be triggered to run automatically upon them entered data into the cell.

With Data Validation, you can require that their entry is exactly 11 characters long, and not accept it if it isn't.
And you can even return a custom message like "You must pad your entry with leading zeroes so that it is exactly 11 characters long".

Yes the text formula would make a new column where the part number is formatted as text. That is ok. I just need to get them as text before uploading into another system.
Users are not inputting one at a time. We are dropping a column of numbers into a spreadsheet template for upload into another system, so the data validation method seems cumbersome. If you do not know of a formula similar to the text one to do the real leading zeroes, then i will see if my superiors will consider VBA option.

 

[Joe4]

If you do not know of a formula similar to the text one to do the real leading zeroes, then i will see if my superiors will consider VBA option.

The issue is that any cell in Excel can either hold a hard-coded value or formula, but never both at the same time!
So you CANNOT have a formula in a cell that they are entering data into. Entering data will simply overwrite the existing formula.
You would either need to use a "helper" column (like you have now), or you would need VBA to fix up the data in place for you.

 

[paquirl]

The issue is that any cell in Excel can either hold a hard-coded value or formula, but never both at the same time!
So you CANNOT have a formula in a cell that they are entering data into. Entering data will simply overwrite the existing formula.
You would either need to use a "helper" column (like you have now), or you would need VBA to fix up the data in place for you.

Right, so my question is do you know of a helper column formula to do the zeroes/11 digit requirement?

 

[Joe4]

Right, so my question is do you know of a helper column formula to do the zeroes/11 digit requirement?

The TEXT formula I posted?

=TEXT(A1,"00000000000")

Is there some issue with that one that wouldn't work for you?

 

[paquirl]

The TEXT formula I posted?

=TEXT(A1,"00000000000")

Is there some issue with that one that wouldn't work for you?

Oh i'm sorry i did not notice it addresses the leading zeroes in same formula. This produces a real leading zero then?

 

[Joe4]

Yes (though in formula mode).

You can see this if you do a Copy -> Paste Special -> Values of the formula over itself, to turn the formula into hard-coded data.
(you don't actually need to do this in using the formula - this is just to prove that it returns a value with "real" zeroes")