# Thread: how do i do this?

1. Registered User
Join Date
Jul 2003
Posts
2

## Unanswered: how do i do this?

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.

2. Super Moderator
Join Date
Aug 2001
Location
UK
Posts
4,650

## Re: how do i do this?

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.

3. Registered User
Join Date
Jul 2003
Posts
2

## Thanks!

That works beautifully, Sathyaram. Much obliged.

4. Registered User
Join Date
Jul 2003
Location
Plainsboro, NJ
Posts
11

## Re: how do i do this?

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...mats/codes.asp

I hope this helps!
Mike

5. Registered User
Join Date
Jul 2003
Location
London UK
Posts
14
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•