Extracting digits from the String

zuchu

Hello,

I'm trying to create a beast mode that would allow me to extract 5 digits that are always coming AFTER words 'Purchase Order'. My issue with it, there is a variable number of digits before depending on the row of data. As an example string might looks like:

ABCDSFF Purchase Order 12345 duewobfd

ADS Purchase Order 12345 1235315132

Every time I want to exclude only the '12345' from the string, and the only other rule is that those number are always after phrase 'Purchase Order'

Is there a way to extract those 5 digits?

GrantSmith

You can use a formula tile and a regexp_replace function to strip out the numbers.

REGEXP_REPLACE(`field`, '^.Purchase Order (\d+).$', '$1')

This will get all numbers after Purchase Order until it doesn't see a number so this would extract 12345 and not ' 12345 1235315132' from your second example as there is a space after 12345'

If you want to explicitly pull 5 digits then you can replace the + with {5}

REGEXP_REPLACE(`field`, '^.Purchase Order (\d{5}).$', '$1')

GrantSmith

You can use a formula tile and a regexp_replace function to strip out the numbers.

REGEXP_REPLACE(`field`, '^.Purchase Order (\d+).$', '$1')

This will get all numbers after Purchase Order until it doesn't see a number so this would extract 12345 and not ' 12345 1235315132' from your second example as there is a space after 12345'

If you want to explicitly pull 5 digits then you can replace the + with {5}

REGEXP_REPLACE(`field`, '^.Purchase Order (\d{5}).$', '$1')