Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: CSV Files (1)

  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CSV Files (1)

    Does anyone know how to add leading zeros to a CSV file?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    Load the file into Excel (if it is not too large).
    Use the text to colums feature to break the data into separate columns. Ensure each columns data type is set to text.
    Then you can manipulate the colum you wat to add leading zero's to. I suggest that you use a fourmula in another spreadsheet to do this eg ="00" & A1 to add 2 leading zero's, and then seelct the whole column and paste backinto the orginal using Paste Special and the values option.

    When all looks OK, save the fiel as CSV and you are finished.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Files (1)

    If you want to save numbers with a fixed number of digits (e.g. 1 becomes 0001, 23 becomes 0023, 456 becomes 0456 and 9876 becomes 9876), you can simply format them in Excel as "0000". Exporting to CSV will preserve this format.

    Or do you want to add leading zeroes to an existing CSV file?

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    I need to add leading zeroes to an entire column in an existing CSV file. Any help would be greatly appreciated.

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    The normal routine for a CSV is, of course, something similar to this:

    "Smith","John","000123456"

    which when loaded into Excel gives you

    Smith John 123456

    However, try this:

    "Smith","John",="000123456"

    I think this is what you are after.

  6. #6
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    I have an entire column in which all cells need to have leading zeroes. For Instance 100 needs to be 0100

  7. #7
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    If the column with the need for the leading zero is the first column, then open up the CSV file with a good text editor, and replace the CR/LF/" entries with a CR/LF/=" entry. (Of course, you would have to change the first line manually.) If it is not, then you may want to replace all ," with ,=".

    This second choice will play havoc with any other numeric values in the file. But they can be addressed via value() formulas afterwards.

    Hope that helps.

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    I have an entire column which has three digit numbers such as 100,108,109 etc. all of which need to have leading zeroes. I tried the formula you mentioned; however, I believe I maybe entering it in wrong. Any suggestions would help

  9. #9
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    Are the entries currently three digits, and you need to have a leading zero to bring them up to four digits?

  10. #10
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    Yes, the entires are three digits and need to be four digits
    Example: 100 needs to be 0100

  11. #11
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    If the column is the first one in the CSV file, then the solution is a minor variation. Instead of replacing CR/LF/" combinations with CR/LF/=", the replace changes to CR/LF/="0.

    If it is not the first column, then the solution may be simpler in a formula within Excel.

    For example, if the entry is in column B, and the first entry is in B2:

    =text(B2,"000#")

    Hope this helps.

  12. #12
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    All the entries are in column G. The formula you gave me worked however it changes the entire column to have the same data. I have multiple entries such as 100, 108, 109

  13. #13
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    Instead of using B2 is there a way to reference all cells within column G? For instance something like =text(*.*,"000#")

  14. #14
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    If the first entry is in G2, then the formula is =text(G2,"000#").

    Copy that formula down, and the cell references will automatically change to reflect the appropriate row. For example, the second entry formula will be =text(G3,"000#"), and the third entry will be =text(G3,"000#")

    Regards.

  15. #15
    New Lounger
    Join Date
    Jul 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (1)

    The CSV will not save formulas :-(

Page 1 of 2 12 LastLast

Posting Permissions

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