Look up - how to apply smart?

Question
1

I want to grab a value from my bottom sheet via a key index number.

I got a sheet with an index number like “19/4116”

Then I got my bottom sheet where the index number is “hidden” among other information in one field. Like this:

BR-Drs. 379/18 (Gesetzentwurf) BT-Drs. 19/4116 (Gesetzentwurf) BT-Drs. 19/4569 (Beschlussempfehlung und Bericht)

At the moment my way to solve it, is to split the column on the whitespaces.
In this sheet I know it must be the new 2. column or the new 5. column to look for a match. (“379/18” or “19/4116”)

Is there a more elegant way to do this?

Replace with RegEx maybe? The index number is consistent with 19/…

But since there multiple index numbers like this in one field I don´t see how to get there.

Any ideas?

2

Yes, you can use regex to create your lookup table. Regex 19/\d\d\d\d should match your index. See attached.

lookup-regex.transform (2.9 KB)

1 Like
3

Oh. You can split columns into rows… :flushed: That solves my problem.

But your RegEx solution looks interesting. But it is required to know beforehand how many matches there could be? Some of my fields were getting quite long, so I had 77 columns when splitting them.

4

If you have an unknown number of indexes, you can use a regex in a Javascript transform.

var r = /19\/\d\d\d\d/g;
var matches = [];
var match;
while ((match = r.exec($(1))) != null) {
   matches.push( match );
}
return matches.join(',');

js

lookup-regex-js.transform (2.7 KB)

There may also be a way to do it using regex in Replace transform. But I am not enough of a regex expert to know how.

1 Like
5

Wow! That ist great. Very, very useful! Thank you very much.

1 Like