Extract certain string between "|"

Hello guys, I have an excel column like the following and I want to extract the string between two “|” that contains “weight”(it may be “weight” or “package weight” or something else, but containing “weight”) to another column. How can I do this? Thank you!

INCH/METRIC: Metric | KEY SIZE: 1.5, 2, 2.5, 3, 4, 5, 6, 8, 10 | NO. OF KEYS: 9 | TYPE: Magnetic Ball End | WEIGHT: 0.9 LB | EAN OR UPC: 82171566004
KEY SIZE: 0.050, 1/16, 5/64, 3/32, 7/64, 1/8, 9/64, 5/32, 3/16, 7/32, 1/4, 5/16, 3/8 | TYPE: Magnetic Ball End | NO. OF KEYS: 13 | INCH/METRIC: SAE | WEIGHT: 0.895 LB | EAN OR UPC: 82171566011
NO. OF KEYS: 22 | KEY SIZE: Includes 56601G, 56600G | TYPE: Magnetic Ball End | INCH/METRIC: SAE/Metric | WEIGHT: 0.725 LB | EAN OR UPC: 82171566028
INCH/METRIC: SAE | KEY SIZE: 0.050, 1/16, 5/64, 3/32, 7/64, 1/8, 9/64, 5/32, 3/16, 7/32, 1/4, 5/16, 3/8 | TYPE: Long Arm Hex | NO. OF KEYS: 13 | WEIGHT: 0.92 LB | EAN OR UPC: 82171566035
KEY SIZE: Includes 56601G, 56600G, 56605G | INCH/METRIC: SAE/Metric | NO. OF KEYS: 31 | TYPE: Master Magnetic | WEIGHT: 2.48 LB | EAN OR UPC: 82171566042
INCH/METRIC: Metric | NO. OF KEYS: 9 | TYPE: Long Arm Hex | KEY SIZE: 1.5, 2, 2.5, 3, 4, 5, 6, 8, 10 | WEIGHT: 0.8 LB | EAN OR UPC: 82171566066

HI @Ionut_Frincu

You can try to use Generate Data Table Activity

Regards
Gokul

Hi @Ionut_Frincu

Have a look on the image

Regards
Gokul

Hello @Gokul001, I want to extract only “weight” for each row, and the problem is that its location is different from row to row… it can be on the starting of the string or in the end or anywhere …

I think Regex can help, but I’m not pretty sure how to write the formula

HI @Ionut_Frincu

Use assign activity

LHS → Create an variable
RHS → System.Text.RegularExpressions.Regex.Match("Input String","(?<=WEIGHT:\s)(\S+)").Tostring.Trim

Regards
Gokul

1 Like

Thank you for your response, but this is the format that I want to extract: | WEIGHT: 0.9 LB | , | Package Weight: 0.137 |

Share the Output @Ionut_Frincu

Try this expression

(?<=WEIGHT:\s)(\S+)|(?<=Package WEIGHT:\s)(\S+)

Regards
Gokul

@Ionut_Frincu

Try this :

Regex.Match(Test,"(?<=WEIGHT: )[\d].[\d]+|(?<=Package Weight: )[\d]+ [\d]+").ToString.Trim

Hello guys, thanks for your response, but maybe I was not too clear :slight_smile:
So, I have a table with one column and each row has multiple attributes between two “|”.

Each row is different from each other and what I want to do here is to extract the attribute that contains word “weight” and put it in the next column, while keeping the rest of the text in the first column.
image

The only similarities from row to row that can help is that each attribute has two “|” and a “weight” string.
Any ideas?

Hi! @Ionut_Frincu, Did you tried the Regex that they guys share with you? Seems like with that will work what you are asking

Yea, and those are not providing the results that I expect…

This topic could help … Regex help tutorial MEGAPOST – Making your first Regex post, Reusable Regex Patterns, Regex Troubleshooting, Sample Workflow and more

Hi @Ionut_Frincu

I hope you are well.

Sounds like you have two posts in one here.

  1. How do I extract this text (Regex)
  2. How can I write the result to column B (datatables)

Sure you can blend into one but you may not get a result as fast as two separate posts.

As for question 1: Forum members can provide you with a strong Regex pattern in minutes/hours on this forum but require strong information to make that pattern.

Samples were provided but sounds like they are not full complete samples. If you know there are various versions of Weight/Package Weight/Total Package Weight etc - then insert those into your sample.

Output, please include this in your original post. Highlight the text in bold within the sample, write out how many results there should be etc.

Pattern/information, what is consistent is a good start and you told us about the “|” symbol and eventually that it needs to contain “Weight”. What about the colon symbol “:”or the units “LB” and the spaces. Literally the more information the better. More information = More Reliable Regex pattern.

These tips will deliver you good results :wink:

I have made a regex pattern for you which should suit your needs from the pieces of information I can gather. The rest was assumptions (and they don’t always turn out :sweat_smile:).

You can preview/test the Regex Pattern here. :

As for Question 2, I would start looking here and make a second post.

Hopefully this helps you :blush: