Leading Zero

[mlew98]

Hi Need help on adding Leading Zero's to a cell.
I am able to achieve this at a cell level but this seemed only to be effective for viewing on the specific cell however when I combine cells either via "CONCATENATE" or "&" functions the leading zero's disappear.

Can someone who knows how to do this please share your expert opinion.

thanks

Micky

[Chris Wall]

Basically the cell is being treated as a number and hence the leading zero is being removed. If you change the cell into a text then the leading zeros should stick around.

One way of doing that is once you have successfully added the zero onto the original number, highlight the area that contains all of the new "numbers", right click in the highlighted area and select "Format Cells", in the "Number" tab select text, then click on OK.

Hopefully this helps!

[mlew98]

Hi Chris,

Thank you for your advise, unfortunately it does not help in my case.

I am getting inputs via Excel and need to consoldiate them into a flat file to be loaded onto a separate system. The inputs come in as either numbers or text.
While I understand that you can change and set the format which was what I have been trying to do it was not very successful for me . Short of re-keying in all the inputs which I want to avoid I am trying to find a smarter solution.


The inputs would come as follows:

Product = Text length of 16 eg."Sample Product"
Part No = text length of 6 eg."1234"
qty = number length w/o decimals of 3 eg. "3"

The translation I am trying to achieve is
"Sample0Product00001234003"

I was able to change the display of "1234" to "001234" but this disappears when I combine the cells.
I have no idea how to replace blank space with 0 for the text inputs.

Hope that this better explain the challenge which I am looking to solve with advise from folks like yourself.

Regards


Michael

[theillknight]

This may be a dirty way of doing it, but can you try this?

Say A1 is the cell of the number. If you always have to add a specific set of leading zeroes (say 3) to that number, then all you have to do is ="000"&A1.

However, if you need the length of the text to always be the same length (like always 8 characters), you might try something like:

=if( len(a1)=1, "0000000"&A1, if len(a1)=2, "000000"&a1, etc.)

Does that work?