Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Leading Zeroes (Office 2000 /XP)

    Hello,

    I just imported the chart of accounts from our accounting software into Excel. Our numbering format is 6 places. For example, they could be: 000100, 004003, 018024, 000098 etc. When exported, Excel dropped the leading zeroes. ei; 100, 4003, 18024, 98.

    What would be the easiest way to get them listed correctly? I tried changing the column to text but that didn't do anything. If I change the number of places, it adds them to the end.

    Thanks!
    Louise

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading Zeroes (Office 2000 /XP)

    It depends on what you are doing with those values. They are being imported as numeric values. If you are not going to be using them to look up using another text value, and just want to display them with the leading zeros, then you can select the cells and then use the custom format 000000. That will display the leading zeros, but will cause problems if you try to use a formula to lookup a text value like 000098.

    If you need to convert them to text values so you can do things like using them for lookups, then there are several possibilities. Just changing the format to text will not convert them to text.

    1- You could import them as text values. However, to tell you how to accomplish this we would need to know how you are importing them. What kind of file are they being imported from, and what process do you go through?

    2- You could convert them into text values after you import them. To do this:

    a- Insert an empty column next to the numeric values.

    b- If the values start in A1, then insert this formula into column B1 (If they start in another cell, adjust the formula accordingly):

    <code>
    =TEXT(A1,"000000")
    </code>

    c- Copy that formula down as far as the codes go.

    d- Select the original cells containing the numeric values and change the Format to Text.

    e- Select the cells containing the formulas and then select Copy from the Edit menu.

    f- Select the first cell containing the numeric values and then select Paste Special from the Edit menu.

    g- In trhe Paste section of the Paste Special dialog box, put a tick mark next to Values and click OK.

    h- Select the column containing the formulas and delete it.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading Zeroes (Office 2000 /XP)

    Hi Legare,

    Thank you very much!
    Louise

Posting Permissions

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