RegEx - extracting text after a specific word

Hi All

I am trying to extract text after the word "tasks" in the below table. Only where Field contains "tasks" do I want the value ".0." or ".1.". I have tried various different Regular Expressions using the RegEx tool but unable to output a value in a new field (it is coming out null or blank). Thanks in advance!

Field

hits.0._source.content.assignedTo

hits.0._source.content.deleted

hits.0._source.content.entities.596

hits.0._source.content.entities.598

hits.0._source.content.lastChange

hits.0._source.content.class

hits.0._source.content.tasks.0.deleted

hits.0._source.content.tasks.0.system

hits.0._source.content.tasks.0.teamToAction

hits.0._source.content.tasks.0.change

hits.0._source.content.tasks.0.id

hits.0._source.content.tasks.0.creationDate

hits.0._source.content.tasks.0.explanation

hits.0._source.content.tasks.0.closingDate

hits.0._source.content.tasks.0.entityIds.0

hits.0._source.content.tasks.0.entityIds.1

hits.0._source.content.tasks.0.status

hits.0._source.content.tasks.1.deleted

hits.0._source.content.tasks.1.system

hits.0._source.content.tasks.1.teamToAction

hits.0._source.content.tasks.1.change

hits.0._source.content.tasks.1.id

hits.0._source.content.tasks.1.creationDate

hits.0._source.content.tasks.1.explanation

hits.0._source.content.tasks.1.closingDate

hits.0._source.content.tasks.1.entityIds.0

hits.0._source.content.tasks.1.entityIds.1

hits.0._source.content.tasks.1.status

Regex

Accepted answers

LukeM

Hi @rebeccarlisle

I always advise to use a website like https://regex101.com/ to write your RegEx before copy and pasting it into Alteryx. The guidance and explanation in there is much more comprehensive.

Using the Parse function in RegEx parse tool you use brackets () to signify which bit of text you want in a field.

I think the following code should work:

.+tasks\.([0-9]).+

From Regex101.com (the green is what we are parsing):

Let me know if you get this working or if you have any other questions.

Luke

All comments

LukeM

Hi @rebeccarlisle

I always advise to use a website like https://regex101.com/ to write your RegEx before copy and pasting it into Alteryx. The guidance and explanation in there is much more comprehensive.

Using the Parse function in RegEx parse tool you use brackets () to signify which bit of text you want in a field.

I think the following code should work:

.+tasks\.([0-9]).+

From Regex101.com (the green is what we are parsing):

Let me know if you get this working or if you have any other questions.

Luke

jaimonsk

In order to extract data after "tasks" the following formula would help

If Contains([Field], "tasks") then Substring([Field],FindString([Field], "tasks")) else "" endif.

I did not understand the requirement after that.Could you give describe it gain with an example?

LordNeilLord

Hey @rebeccarlisle

I know you have the solution for this but I think you might find an easier way to approach this JSON if you use text to columns first then you can crosstab your results easier.

JSON Parse.yxmd

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335