Convert return separated text to table

[Sccardais]

I want to convert text such as the example below to a table. The resulting table would have 9 columns. Every line in the source is separated by a single ¶.

Using the example text below, "Williams (WMB)" would be the first column in the first row. The number 4.44 would be the last column in the first row. "PPL" would be the first column in the second row. "Southern" would be the third and so on.

I tried a search to find any number + return + any upper case letter and replace with ** to separate but the result removed the number and the upper case letter.

I'm sure there's a way to retain some of the search characters but I have no experience with this and didn't find any examples in Nisus online documentation.

Any help would be appreciated.


Williams (WMB)
Oil & Gas - Production and Pipelines
$21.15
5.32%
54.40%
8.21%
31.94
1.25
4.44
PPL (PPL)
Utility - Electric Power Distribution
$22.66
5.21%
77.73%
22.03%
12.95
1.92
15.23
Southern (SO)
Utility - Electric Power Distribution
$47.93
5.15%
99.59%
14.21%
13.99
1.71
12.88

[phspaelti]

Let's start with your most immediate question: How to search for a <return> between a number and a capital letter. As you found out, if you write a find expression [number]<return[Capital letter], the replace expression will wipe out the number and the capital letter. The trick is to use the "Preceded By" and "Followed By" which can be found in the "Match" submenu in the Find Dialog. Put the "Preceded By" parentheses around the [number] wildcard and the "Followed By" around the [Capital letter] wildcard and then these parts will be omitted from the replace expression.

This is a very handed trick.

[phspaelti]

And now to the general problem.

I take it your approach is going to be:

1. Find and Mark the row ending returns
2. Replace all other returns with tabs
3. Select everything and convert to table

That's a good approach, especially if you have a good way of recognizing the line ending returns. But a more general approach is to use the "Repeat:N Times" symbol. With that approach you first find blocks of 9 lines and then replace the returns in the selections with tabs. For this to work correctly you will of course need to select 9 lines excluding the last return (since you want to keep that one). So now the procedure becomes

1. Find blocks of N lines, excluding the last return
2. Replace all returns in the selection(s) with tabs
3. Select everything and convert to table

I append a macro which does this.

[Sccardais]

Thank you for both of your replies.

I'll post results.

Thanks again.

[Sccardais]

Thank You ... phspaelti

Both of your solutions worked first time!

The PrecededBy and FollowedBy is going to be very, very handy in the future.

The macro worked but I will need to study this to see WHY it worked!

Thanks again. This will save me a lot of time and make some things possible that weren't before.

[Sccardais]

Thank You ... phspaelti

Both of your solutions worked first time!

The PrecededBy and FollowedBy is going to be very, very handy in the future.

The macro worked but I will need to study this to see WHY it worked!

Thanks again. This will save me a lot of time and make some things possible that weren't before.

[phspaelti]

The macro worked but I will need to study this to see WHY it worked!

The crucial line of the macro is the one which selects N lines of text, excluding the last return. For example if you want 9 columns you have 9 lines of text with 8 returns. So the find expression will need to select 1 line followed by 8 repetitions of <return><line-of-text>. In Powerfind the expression will look like this:

Find_9_lines.png (65.32 KiB) Viewed 8813 times

In Powerfind Pro the same looks like this:

Code: Select all

Find '^.*(\n.*){8}$', 'Eas'

Finally to get this to work with a user input, one will need to replace the "8" with a variable that can interpolate the desired number. I've used $c for the variable. For interpolation to work it is necessary to replace the single quotes with double quotes. But this in turn means that the backslash and $-sign need to be protected:

Code: Select all

Find "^.*(\\n.*){$c}$$", 'Eas'

Hope this helps

[Sccardais]

Philip:

Yes. It does help. Thank you for the explanation.

ScottC