Parsing a text string after a word

Question

Hello everyone.  I have not used the parse to text (outside of simple text to columns) or the RegEx tools much and i am struggling with the above.  I have a column  that looks like the data in the column labeled Original.  I need it to look like Columns c - f.  Every port starts with OC but I can not figure out how to do a starts with.  I tried to do an after "on" but since None has on in the word, that didn't work.

Thank you!

Allison

test string problem.xlsx

Slayer · Accepted Answer

If you think about doing two passes, it’s actually quite straightforward:
First pass, use a Formula to remove the quotes, then split on the commas using Text-to-Columns
Then add another Text-to-Columns, splitting on the space.
Clean up using a Select tool to drop unwanted fields

(Sent from my phone, so can’t provide an example

NicoleJohnson · Accepted Answer

@aberry0613,

See attached for a solution I was just about to post that illustrates @Slayer explanation! :)

You'll potentially need to Cross-Tab your data once done to get it back into the same format as your example solution, so let us know if you need some additional assistance with that!

NJ