Parse fields based on multiple keywords into multiple new fields

I have been attempting to parse a long string of text that a variety of keywords that may or may not be in the same order from row to row. I first attempted to replace each keyword with a pipe and then use the text-to-columns tool to break them out into separate fields. The issue with this is that I don't necessarily know which keyword the parse refers to. I attempted also to throw in an equation to count the number of characters before the keyword to know which one it was (i.e., to know the order) but that got very messy.

I'm not great at REGEX but I believe this can be done easily using the RegEx tool. Is there some way to do this with RegEx so that, knowing the keywords I want to parse on, that fields are created for each new keyword with the text that corresponds with it?

I have attached an example input and Desired Output (as two text inputs in a blank workflow).

Thanks!

Input and Desired Output.yxmd

Regex

Preparation

Accepted answers

MichaelSu

@NeilFisk ,

Please see attached workflow and let me know your thoughts. Rather than using RegEx, you can try to use the Split to Rows.

Thanks,

Mike

Parse_Fields.yxmd

All comments

apathetichell

Interesting - are the keywords always set or are they fluid? is there always a clear demarcation of the information you want after the keyword - ie. does it always end in .?

MichaelSu

@NeilFisk ,

Please see attached workflow and let me know your thoughts. Rather than using RegEx, you can try to use the Split to Rows.

Thanks,

Mike

Parse_Fields.yxmd

NeilFisk

Mike,

I can't use the : as the delimiter as it may be used in the sentence and not contained directly after the keyword. However, I was able to use RegEx with a simple find/replace to find something like ISSUE: and replace it with ISSUE|, etc. This allows me to use the Text To Columns tool as you suggested. The important piece I was missing was the use of the multi-row formula. After doing this, I was able to get the results I needed.

Thanks,

Neil

NeilFisk

Keywords are always the same and generally have a : afterwards, however, : may be present elsewhere in the text, so although Mike suggested to use that as the delimiter to use text to columns, I had to change that first. I can't rely on the paragraph always ending in a period either, as they may have missed one, or there may be multiple sentences to parse out. Mike had the right direction with a multi-row formula.

I still think that some form of REGEX would still work, but I'm no expert in that.

Thanks!

apathetichell

glad to hear it worked out @NeilFisk - as an fyi - not sure if you adjusted it on your regex attempts but | is part of regex syntax so to use it as just a character you'lll have to use \| - this could be part of the problem why your regex wasn't working...

NeilFisk

That's not an issue as I was just using a simple replace option for REGEX and the pipe was added not in the expression but as the replacement text. You can see the screenshot below. Although this works, I was hoping someone would also show a solution truly using REGEX.

apathetichell

Something like this? Let me know if this is heading in the right direction - id doesn't have the non-matched as a separate category, but this is how I would approach it via regex.

THE REGEX WAY.yxzp

NeilFisk

Rather complex with the macro. I was thinking that the RegEx tool under the Parse tab could be written in such a way to contain the entire logic.

apathetichell

@NeilFisk Yes. It's possible with regex parse but the way | and options work under regex parse, I believe you would need 20+options under the parse tool to recreate the optionality provided by my macro.

Here's an example - let's say a field has issue: subject: comment: and the second row has comment:

to match both of these you'd need parse to say:

(.*)(issue: .*)\s{2}(subject:.*)\]s{2}(comment:.*)|(.*)(comment:.*) - and it kind of goes from there. - that would create four fields and match only those two cases. Anything not matching all three or not only comment would be null().

At it's best it would be the same as text to column/splitting based upon two spaces. or two spaces and a :... - and I'd personally prefer swapping \s{2} for something and going from there.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335