I'm no exper in Excel ...

You can use this formula in Col B

=CONCATENATE(REPT(0,8-LEN(A1)),A1)

Cheers

Sathyaram

Originally posted by Dusty
Is this possible?
I have fields in a spreadsheet that need to be a fixed length (i.e., Column A must be 8 characters). I was given a file with tens of thousands of lines of data that looks similiar to this:

Column A
00001111
234
12345678
91919
00002
5

What I need is for all that is not 8 digits long to be preceded by however many zeros as needed (e.g., 234 would be 00000234). Short of a tedious concatenation exercise, how might I approach this?

Thanks.

The solution above using the Rept() function is probably best for your needs. But another aproach is to change the number formatting of the cells.

To do that you would:

(1) Select the range of cells in question
(2) In the Toolbar, choose "Format" and then choose "Cells..."
(3) Choose the "Number" tab, and then within the list box choose "Custom".
(4) Within the "Type" box, you will type in "00000000" (without the quotation marks), then click <OK>.

I know that this seems like a lot of work, but it's not as hard as it looks. If you're not going to ever use Excel much, then you might not want to bother, but I think this is probably worth learning. Number formatting is sort of fundamental in Excel and can really save a lot of time once you get the hang of it.

I would print up the following two links and keep them around for handy reference, if you ever come across this kind of issue again. The first link is a full description of all the formatting codes, while the second is a very basic tutorial on how to use them:

(1) http://support.microsoft.com/default.aspx?scid=/support/excel/content/formats/codes.asp

(2) http://businesssoft.about.com/cs/microsoftexcel/a/aa051503a.htm#b

I hope this helps!
Mike

You can also use the TEXT function to achieve formatting changes, and the VALUE function to extract the number from a text string.
Excel has unusually good help files for functions (type in TEXT FUNCTIONS) and a combination of these, I've found, will fix almost any formatting problem.