Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deliminating Cells (Excel 2000)

    I am trying to deliminate columns of information but the info in column B (on attached spreadsheet) contains symbols "II" and when you go do deliminate, it doesn't recognize the symbols in that column. I need to have the information before and after each "II" into it's own column. After each "II" there are the following words:

    userid
    Name
    Company
    File Downloaded

    These should be the column headers and the information directly after each word is the information we need in that particular column. For example:

    Column A: Subject
    Column B: User id
    Column C: Name (wI'll also want to separate first from last name after I get everything else formatted)
    Column D: Company
    Column E: File downloaded

    Any ideas? THanks in advanced!

    Trisha
    Attached Files Attached Files

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

    Re: Deliminating Cells (Excel 2000)

    Here is a partial solution.

    Select the cells you want to modify (B2:B45)
    Go to the Visual Basic Editor (Alt+F11)
    Open the Immediate window (Ctrl+G)
    In this window, type:
    Selection.Replace vbCrLf, vbTab
    followed by the Enter key.
    Next, type:
    Selection.TextToColumns Tab:=True
    followed by the Enter key.
    Go back to Excel.
    The cells have been split.
    You must remove superfluous columns, adjust column widths and add column headings manually.

    There is a problem in row 27 (no Name and Company), so it will not align correctly with the others.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Deliminating Cells (Excel 2000)

    Hi Trisha,

    The "II" symbols you referred to are paragraph marks - it looks like your data was imported from a text file.

    If you aren't comfortable with the VBA approach, you could save the file from Excel as text and open it in Word. In Word you could then could do a search & replace to change the para marks (^p) to tabs (^t). Then you'd probably also want to replace the tabs followed by quotes and tabs again (^t"^t) with para marks, then the remaining tabs followed by quotes (^t") with tabs (^t). Finally, reimport the file into Excel with tabs as the delimiter. You'll still need to fix row 27, as noted by Hans.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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