parse for a certain word or get value at certain string

[eaglepath]

In the Data below i want to POL and corresponding value from Part so basically where ever is POL placed we need to get same number of value from part column:

Data:

Ops	Part
key-ID-ISSUE-POL-COT	856-658-985-963-458
key-ID-ISSUE-POL	968-548-785-9635
key-ID-ISSUE-POL-COT-DT	4563-568-475-96242-544

Result:

Ops	Part
POL	963
POL	9635
POL	96242

[Carolyn]

This formula should work:

GetPart([Part], "-", REGEX_CountMatches(REGEX_Replace([Ops], "(.*)(POL).*", "$1$2"), "-"))

1. REGEX_Replace([Ops], "(.*)(POL).*", "$1$2")
   1. Remove everything after "POL"
2. REGEX_CountMatches([Output from #1], "-") )
   1. Count the number of "-". This will establish where "POL" is located. 
3. GetPart([Part], "-", [Output from #2])
   1. In the Part # field, using a delimiter of "-", find what's in the location from #2
   2. Note, GetPart is 0 based. In your examples, "POL" is in the 4th spot, but in GetPart, to find the 4th spot, you would use "3". Since we're counting "-" in Step #2, this will work for the 0 based positioning

 

Edit: 

Revised WF attached. I split up the 3 formula parts into separate formula spots. It'll be easier to see each step's output (vs the final combined formula, which is what's above)

[ChrisTX]

One option attached

 

Chris

[eaglepath]

Forumla 3 and 4 (get part) is having an issue please check

[cristiane_dsc]

I have managed to get the desired output exactly as you wrote. I hope this is what you are looking for.

[eaglepath]

Christiane_DSC  is it doing by the position or if the position changes it will pick this regardless?

[binuacs · Accepted solution]

@eaglepath another method

[Carolyn]

@eaglepath - What error are you seeing with my formulas 3 and 4? It's working fine for me - happy to help troubleshoot your error if you show it!

[flying008]

Hi, @eaglepath 

There is a formula of dynamic position for you:   [ It can used in any version. ]

Spoiler

RegEx_Replace([Part], '^((?:[^-]*?\-){' + ToString(REGEX_CountMatches(Left([Ops], FindString([Ops], 'POL')),'-')) + '})(\d+?)(-.+|$)', '$2')

[Yogesh1795]

You use simple use (\d+ - \d+ - \d+ -) (.*) - (.*) and get the $2 Value in regex replace tool.