Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text with leading 0 to a number (Excel 2000)

    I have a list of bank numbers exported from Quickbooks into Excel. The numbers have changed to text. The text numbers are not a specific length - up to 11 characters and some with leading zeros - up to 3 zeros. How do I change the text to a number and keep the zeros, as the bank needs this format?

    Hope someone can help.

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

    Re: Text with leading 0 to a number (Excel 2000)

    If I understand you correctly, the bank numbers are of varying length including the leading zeros. In that case, it is not possible to convert them to numeric while keeping them displayed as they are now (I think).

    If you need the numeric value, you can add a column (which may be hidden) with formulas like =VALUE(A1) if A1 is one of the text numbers.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text with leading 0 to a number (Excel 2000)

    What about putting the number and formatting (format - cells - custom) with something likeno quotes)
    "00000000000"

    This will always display an eleven digit number and will add zeroes in the front to fill in to any numbers less than 11 digits

    Steve

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text with leading 0 to a number (Excel 2000)

    Yes, the text is a varying length including the leading zeros. So I guess I can't convert them and keep the correct number of leading zeros. The only thing I thought might be possible is to somehow count the # of characters in each text string then add the correct # of zeros when it is converted. But I don't know how to do something like that in the way of a formula.
    Thanks for you help.

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

    Re: Text with leading 0 to a number (Excel 2000)

    Is there a specific reason why you want to convert the bank numbers to numeric? If you have a varying length with a varying number of leading zeros, the bank numbers are essentially text.

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text with leading 0 to a number (Excel 2000)

    Apparently the bank requires them in numeric format - it is for auto deposit for pay cheques to the employees accounts.

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text with leading 0 to a number (Excel 2000)

    Thanks Steve - but all the numbers are a specific length - as required by the bank. Some have leading zeros - others haven't.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text with leading 0 to a number (Excel 2000)

    But if they NEED NUMBERS, they should NOT need leading zeroes which are essentially NON numeric.

    The options seem to be:
    1) what Hans suggested: 2 columns text for display and a second for the VALUE
    2) Put EXTRA leading zeroes making ALL the numbers 11 digits (my suggestion)
    3) MANUALLY or via a macro to change the displayed format for every single number to the proper number of leading zeroes. You could create essentially 11 different custom formats of 1 to 11 zeroes for the number of "required digits".

    Steve

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

    Re: Text with leading 0 to a number (Excel 2000)

    Are you sending them the .xls file, or a txt or csv file created from the workbook? If you are sending them the .xls file, then the bank should be able to tell you exactly what they want and how to do it. They may just mean that they want a text cell with only numbers in it as text with the leading text zeros. Converting the text to numeric is no problem. If the bank is processing this with a program, then it should not make any difference if the leading zeros are or are not dislayed. If they are processing it manually with people, then it should not make any difference if the cell is text or numeric.

    The only way to convert the text to numeric and display the values with the leading zeros would be to use a different custom format on every cell.
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text with leading 0 to a number (Excel 2000)

    Thanks Legare - I will get in touch with the Bank again tomorrow.
    Thanks all for your suggestions.

Posting Permissions

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