{Problem Solved, Thanks Duane} Parsing a text file
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: {Problem Solved, Thanks Duane} Parsing a text file

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    {I edited my note b/c of the way it was displayed. pretend each of the periods in quotes below are really spaces.}

    I imported a text file, and want to split it up into columns. It is all in column A right now. I want to insert a "column break" if two spaces are encountered. i.e., if column A = "abc...def.gh......ijk" then I would have "abc" in b1, "def.gh" in c1, and "ijk" in d1.

    I have tried one method: in b1, i type =find("..",$a1,1) and in c1 i type =find("..",$a1,b1+1) and then fill right for several columns. Then I can use left and right functions to do the rest. This works fine, except if I have several spaces in a row: I want this to mean a single column break. But in my method, it would detect each set of two spaces independently. (i.e. if there were five spaces in a row, it would indicate 4 column breaks). I couldn't figure out how to maniuplate the find function to get around that.

    Any ideas? Thanks in advance.

    [ This Message was edited by: tk19 on 2002-04-11 14:02 ]

    [ This Message was edited by: tk19 on 2002-04-12 11:51 ]

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Instead of doing that, why don't you use Excel's builtin parsing abilities?

    TO use it, open Excel then browse to your file making sure that the dialogue box is set to View "All Files". When you try to open the text file, Excel will automatically parse the file for you by taking you through a wizard. The second page on the Wizard allows you to use SPACE as the delimiter and there's a checkbox there which allows you to treat consecutive delimiters as one (or not in your case).

    HTH

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, thanks for your reply.

    That won't work, because it will put columns in on single spaces. I only want a column break if 2 or more consecutive spaces are found.

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use Data|Text to Columns...

    - highlight the column
    - go to Data|Text to Columns
    - put check in the box for "Delimited" and hit next
    - put check in BOTH the space box AND the other box, AND THEN enter ANOTHER space in the other box, and hit next and then finish

    OR, you could use Find/Replace to find double spaces, and replace them with an oddball character that you know won't come up anywhere else in your actual data, such as a # or | (don't use anything that Excel might interpret as a function operator (such as a * or &)

    Then you can run Data|Text to columns and put that oddball symbol character you chose in the Other box

    It worked with my test text, let us know if this works with your actual data.

    _________________
    Regards, Duane

    [ This Message was edited by: Duane on 2002-04-11 14:38 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Duane,
    Thanks! solution #2 worked great.. I should have thought that one up myself..
    tk19

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com