Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Worthington, Ohio, USA
    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:

    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!

    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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
    Canberra, Australian Capital Territory, Australia
    Thanked 406 Times in 335 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.


    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