Iterative text cleanup via calc or custom function...

[Macgyver]

This is one of those things I know I could do if I could just find an example of something similar. In lieu of a more robust connection with another tool we use at work (which is ancient and not going to be updated), I'd like to create a quick method for pasting in some data from the old tool and cleaning it up and just showing the data we care about (via an auto-enter calculation step).

The data I can copy/paste is multiple lines of text, with the individual field data separated by tabs. I want to paste this into a text field and strip out all but two values per line (the positions of which I can always determine by counting the tabs.

So, each line would look like:

0001 {tab} X {tab} 111-1112 {tab} B {tab} FINISHED GOODS LABEL {tab} 03 - PROD RLSD {tab} 1.000 EA {tab} etc....

I only want to keep the 3rd & 5th values (for each line), and lose everything else. I could easily create a script to do this, but I want it to be elegant and want to learn how to do this via some kind of recursion in a calculation or custom function. Ideally, it would be smart enough to only affect lines that had multiple tabs in the first place (so I could paste a line like the one above at the bottom of other text that had already been formatted).

Any suggestions, or examples of something similar enough to get the lightbulb in my brain to click on?

Edited November 6, 2006 by Guest

[Lee Smith]

Wouldn't it be easier to do this in a text editor such as TextWrangler?

[The Shadow]

Here's a sample of how this might work.

Tabbed.zip

[Macgyver]

Works great, and was easily understood, except for the actual recursion part. Apparently I need to try to re-understand that last function line when I'm not so tired.