Need Excel Aid

Discussion in 'Mac Apps and Mac App Store' started by zelmo, Jan 4, 2006.

  1. zelmo macrumors 603

    zelmo

    Joined:
    Jul 3, 2004
    Location:
    Mac since 7.5
    #1
    I have a group of 2,006 cells, each one containing a number that is anywhere from 3 to 6 digits in length. I need an easy way to convert each number to a 7 digit code by adding one or more zeroes to the front (change "376" to "0000376"). Ideas?

    Back story (if you care about my pain):

    A client gave us an Excel file to be used for a mailing containing a letter and an ID card. In the original file, they had a seven digit code for each of the 2006 names in the file. All of these codes were preceded by from 1-4 "0" characters so that they were all 7 digits long, most likely by setting up that column of cells as text fields. We exported the Excel file into our mailing program to do our de-dupe and pre-sort for postal discounting. This action retained the 7 digit code. However, we had to provide a 3rd party vendor with an Excel file that was sorted to match our mailing, since they were producing the ID cards which we wanted in sequence with our printed letter (the names on the letters and cards have to match up). Keeping them both in the same order is kind of crucial to efficiently marrying them up.
    When we export the three cells they need for each name, this particular field is stripped of all the extra preceding zeroes. I don't know about you, but typing '000 into 2,006 records is not exactly my idea of fun. help me.
     
  2. Applespider macrumors G4

    Applespider

    Joined:
    Jan 20, 2004
    Location:
    looking through rose-tinted spectacles...
    #2
    Using 'Text to Column' and telling it that it's text before exporting it doesn't help in keeping the leading 0s?

    If not, then you could do an IF formula - assuming that your data is in column A then something along these lines should work

    =if(len(A1)=3,"'0000"&A1,if(len(A1)=4,"'000"&A1,if(len(A1)=5,"'00"&A1,"'0"&A1)))

    That should produce another column

    234 '0000234
    4567 '0004567
     
  3. iSaint macrumors 603

    iSaint

    Joined:
    May 26, 2004
    Location:
    South Mississippi y'all, near the water!
    #3
    Format > Cells > Number > Custom

    then enter as many zeroes as needed to cover the required format, seven in your case
     
  4. zelmo thread starter macrumors 603

    zelmo

    Joined:
    Jul 3, 2004
    Location:
    Mac since 7.5
    #4
    Thank you Applespider and iSaint, for the assistance. I used the easiest advice, which worked like a charm. If only our operator had checked the field before we dumped a $6,000 job in the tank....
     

Share This Page