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: OpsPartkey-ID-ISSUE-POL-COT856-658-985-963-458key-ID-ISSUE-POL968-548-785-9635key-ID-ISSUE-POL-COT-DT4563-568-475-96242-544 Result: OpsPartPOL963POL9635POL96242

parse for a certain word or get value at certain string

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

Parse

Text Mining

Accepted answers

binu_acs

@eaglepath another method

Pase_28082024.yxmd

All comments

Carolyn

This formula should work:

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

REGEX_Replace([Ops], "(.*)(POL).*", "$1$2")
1. Remove everything after "POL"
REGEX_CountMatches([Output from #1], "-") )
1. Count the number of "-". This will establish where "POL" is located.
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)

New Workflow1.yxmd

ChrisTX

One option attached

Screenshot 2024-08-28 144728.png

Chris

parse for a certain word or get value at certain string.yxmd

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.

Captura de tela 2024-08-28 171404.jpg

eaglepath

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

binu_acs

@eaglepath another method

Pase_28082024.yxmd

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.

OTrieger

@eaglepath

This is an example for a post that I will not bother helping. There are several people that suggested workable solutions, I tried each and worked correctly, and you are just nattering that they are not working not even sharing the issue that you are facing from your end. If it is working for others and not for you then you are doing something wrong.

You have here at least 4 workable solutions. Good luck.

eaglepath

@binuacs Thnks for the solution worked really well.

eaglepath

@20-Binuacs

Your solution worked really well. Thanks

Can you please explain this formula as well? Thanks!

binu_acs

@eaglepath

hope this helps
REGEX_Replace([Ops], "^(([^-]+-){"+TOSTRING([Position]-1)+"})([^-]+).*", "$3")

[Ops]:

This is the input string on which the regular expression will be applied.
"^(([^-]+-){"+TOSTRING([Position]-1)+"})([^-]+).*":

This is the regular expression pattern that the function uses to match portions of the input string.
Let's break down this regular expression:

"^": Matches the start of the string.

"(([^-]+-){"+TOSTRING([Position]-1)+"})":

"[^-]+": Matches any sequence of characters that does not include a hyphen (-).
"[^-]+-": Matches a sequence of characters followed by a hyphen.
"{"+TOSTRING([Position]-1)+"}": The TOSTRING([Position]-1) dynamically calculates a number based on the Position field minus 1. This number dictates how many occurrences of the preceding pattern ([^-]+-) to match. The result is the first N-1 segments before the hyphen.
"([^-]+)": Captures the segment immediately following the N-1 segments of hyphen-separated values. This is the part of the string you're interested in.

".*": Matches the rest of the string, but it's not captured because we are only interested in the previous capturing group.

"$3":

This indicates what the replacement string should be. The $3 refers to the third captured group in the regular expression.
In this pattern, $3 corresponds to ([^-]+), which is the N-th segment of the hyphen-separated values.


Example Walkthrough:
Suppose [Ops] is "A-B-C-D-E" and [Position] is 3.

"^(([^-]+-){"+TOSTRING([Position]-1)+"})":

TOSTRING([Position]-1) = 2, so this will match the first 2 hyphen-separated groups "A-B-".
"([^-]+)":

This will capture the next segment, which is "C".
".*":

This matches the rest of the string, "D-E", but this is not captured.
"$3":

The final output will be "C", which is the third segment in the hyphen-separated list.
Summary:
The REGEX_Replace function in your case is used to extract a specific segment from a hyphen-separated string based on the Position value. It finds the Position-th segment in the string and returns it as the result.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335