Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Question 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. #2
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Posts
    2

    Thanks!

    That works beautifully, Sathyaram. Much obliged.

  4. #4
    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

    (2) http://businesssoft.about.com/cs/mic...a051503a.htm#b

    I hope this helps!
    Mike

  5. #5
    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
  •