Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formatting text field

    Greeting,

    Iím trying to submit a required state report as a text in certain format. I used Unidata to pull the required field as flat file and import the data to Excel 2007 successfully. The file has about 25 column (FName, MI, LName, SSN, DOB etc.)

    The state wants the file formatted as, letís say 5 spaces between FName and MI, and 4 spaces between MI and LName and 4 spaces between LName and SSN ,3 spaces between SSN and DOB etc. and submit it as a text without any file extension (so they can open the file using any text editor like NotPad.

    What is the best way to accomplish the formatting?

    Thank you in advance.

    OCm

  2. #2
    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
    You can create a concatenation into a new sheet and save this sheet.

    It would be something like:
    =Sheet1!A1&rept(" ",5)&Sheet1!B1&rept(" ",4)&Sheet1!C1&rept(" ",4)&Sheet1!D1&rept(" ",3) etc

    You may need to format the DOB (with Text) to get the desired output.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve for your reply & suggestions....

    OCM

Posting Permissions

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