Number formatting problem

[lulu319]

I'm having trouble formatting numbers that I copy from another document.

For example, If i copy a number like this 636-10-14, I would like it to appear as 0636-10-14. I want to add leading zeros to fill a form of xxxx-xx-xx.

I tried doing the normal formatting -> numbers -> custom but it didn't work. I figure it didn't work because the '-' and the fact that they are copied.

Any suggestions would be greatly appreciated!

[nemo74]

In the colum next to your copied cells paste this and drag down

=LEFT(0&A1,LEN(A1)+1)

[Ed_Collins]

If some of your numbers are already in the proper xxxx-xx-xx format, then nemo74's elegant little formula will also give these numbers an extra 0... which you might not want.

If so, then you might want to use this formula:

=IF(LEN(A1)=9,0&A1, A1)

This adds an IF check to his code, to first check to see if the length is 9. If so, it adds a zero, otherwise it leaves the number alone.

[Kevin UK]

Hi lulu319

Or another way, this will convert it to a number.

=--SUBSTITUTE(A1,"-","")

Then format the cells as: 0000"-"00"-"00