Parsing a string of text

[jesuisbo]

Hi all,

I'm wondering if there's a way to parse the below string of text.

Jury1989 Jan Klaase VARA Jantje Piet NRC Kees Kiewier VOO Imme van den Zande AVRO Olaf Kaan Juryvoorzitter Hanneke Derken NOS 

I'm looking for the purple highlighted names. After each name their company is listed in capitals, is there a way to write a RegEx formula that looks for words in lowercase before a word in uppercase?

I'm clueless...

Thank you! 

[NickSm · Accepted solution]

Hey @jesuisbo 

Making some assumptions here but if you aren't necessarily needing the company names then it might be easy to do a Regex Replace to swap any strings of multiple uppercase letters out for a delimiter that you can then text-to-columns or rows. If that's information you need to keep then you might need to take a little bit different approach, but here's an example that can at least give you some ideas.

Workflow attached

[DiganP · Accepted solution]

@jesuisbo You can use a regex formula as @NickSm mentioned. Here's another way to do it without regex. It parses the Name and company name into 2 segments. Attached is the workflow. 

[estherb47 · Accepted solution]

Hi @jesuisbo 

Another approach would be to clean up the leading text+number with a formula tool and RegEx_Replace, and then a RegEx parse on the uppercase words

I use Tokenize into rows. This expression, (.+?)(?:\s\u{2,}\s), says to use the all-uppercase words as the delimiters.

Let me know if that helps.

Cheers!

Esther

[jesuisbo]

Great, this worked, thank you!!