{Problem Solved, Thanks Duane} Parsing a text file

[tk19]

{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

 

[Mark O'Brien]

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

 

[tk19]

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.

 

[Mark O'Brien]

Ah.

 

[Duane]

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

 

[tk19]

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