Use String function to filter data

[ulrich_schumann]

Hi all,

I have an Excel sheet with one column of short text. Within this short text there is sometimes a 'number combination', where I want to apply a filter.

- this number combination can show up anywhere within the short text (beginning, middle, end)

- short text data type is V_WSTRING

- this number combination is always 8 digits

- this number combination always starts with '10'

- the number combination would look like '10xxxxxx'

What needs to be put into the filter tool to filter only on these entries containg such a number comination?

Optional: For the future the '10' might be replaced - would there be also a more generic way to just search of 8 numbers?

Thanks for your help.

[jdunkerley79 · Accepted solution]

You can REGEX Match. 

Two ways to do this

Use a REGEX Tool in match mode to add a TRUE/FALSE column and then filter. The expression will be:

.*10\d{6}.*

This will add a new column you can filter on.

Or more directly use a Custom Filter with the expression:

REGEX_Match([Test],".*10\d{6}.*")

Sample of both attached

[JohnJPS · Accepted solution]

@jdunkerley79 is spot on as always; a similar approach is...

REGEX_CountMatches([Field1], "10\d{6}") > 0

... for the first question, and ...

REGEX_CountMatches([Field1], "\d{8}") > 0

... for the optional.

See the attached workflow, and experiment with diffferent test data.

Hope that helps!

[ulrich_schumann]

Thank you very much both for your quick help!!