1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Fixed Length in Excel

Discussion in 'Business Applications' started by KHolloman, Aug 13, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. KHolloman

    KHolloman Thread Starter

    Joined:
    Jul 22, 2004
    Messages:
    896
    Not very fluent in Excel. I am setting up a positive pay format for my company to transfer check information from my PC to a bank via an ASCII Flat-File. Question is, I have so many columns that need to be set at 10 characters fixed length.

    Is there way for this to be done? Example if I key in a check for $1.95

    I want it to automatically put it as 0000000195.

    Can this be done?

    Thanks
     
  2. Sponsor

  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    Just right click each column and choose "FORMAT CELLS"

    Select "Custom" from the list of selections and in the textbox labeled "Type" just enter 0000000000


    Rollin
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Hi Rollin ; not sure, I think that might only make them appear in that format, rather than be in that format.

    Some event code in the worksheet module might be required (rightclick the sheet tab & select "View Code" to access it), such as

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Trap for multi-selection
    If Selection.Cells.Count > 1 Then Exit Sub

    'Run code only if change is made in column A ; adjust to suit
    If Target.Column <> 1 Then Exit Sub

    'Trap for DELETE key (clear cells contents)
    If Target = "" Then Exit Sub

    Application.EnableEvents = False
    Target = Right("0000000000" & Target, 10)
    Application.EnableEvents = True
    End Sub

    The required column(s) would need to be preformatted as text.

    ???,
    Andy
     
  5. KHolloman

    KHolloman Thread Starter

    Joined:
    Jul 22, 2004
    Messages:
    896
    Ok.. I had someone else give me the type 000000 advice, but I will look into both of these.

    Thanks guys (y)
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    Even though only the original number appears in the formula bar, when the file is saved as a .txt file it will save the values with all leading zeroes. The same applies if you were to copy and paste the cells into a text file. If he needs the file to remain in Excel format he can save the file to text as I mentioned above and then re-open the text file using Excel. The third step of the text import Wizard will allow you to select the data format for each of the columns you are importing. If you select text as the format, Excel will re-import the values including the leading zeroes. I interpretted the original post as having a need for a ASCII text file to be created.


    Rollin
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/261661

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice