Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert leading/trailing spaces in text

    I need to upload information from a pc file to an AS/400 file. There are 5 fields in this file, the first 3 have 10 characters each and the last two have 15 characters each. Information entered in the Excel file has varying lengths in each field, but none fill the 10 or 15 characters. When I import the data, I need to fill the extra characters by inserting blank spaces behind the data in the first 3 fields and in front of the data in the last 2 fields. There are numbers in the sheet, but they all have to come up as text fields, no number formats allowed.

    Is there any way to do this either through a custom format or through a macro. In the past for smaller uploads, I've just used concatenate and figured out how many spaces I needed and did it manually. This time it will be done on a much larger scale and I don't want it to be manual if I can help it.

    Any ideas? Thanks in advance!
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert leading/trailing spaces in text

    I would find some empty columns beside the data you have to export.

    In three columns type the following formula (or type once, copy twice[img]/w3timages/icons/smile.gif[/img]) in G4:I4
    <pre>=B4 & REPT(" ",10 - LEN(B4))
    </pre>

    (where B4 (and B5 & B6...) is the cell that contains the data you want to export)
    in J6:K6 enter
    <pre>=REPT(" ",15 - LEN(E4)) & E4
    </pre>

    (where the data you want to export is in E4:F4)

    Thes formulas will return text strings in the format you require. Copy that formula range down as many rows as you have data in, then copy/paste special/value to convert the formulas to their string values, and you can export that portion of the file, rather than your original data.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert leading/trailing spaces in text

    EXACTLY what I needed! Thanks! I had the LEN to get the remaining spaces, but I wasn't familiar with the REPT function.

    Thanks again!

    Brett

Posting Permissions

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