Regex to parse concatenated field

[Fred22]

Hello.

I have attached sample data that I need to parse. Column B ParseField is the field that needs parsing. The data before looks like this:

Request TypeConfig

Region: North America ConfigType: Windows 10 (WIN10) Business Case: Need Upgrade Mac Address: 1234567 Quantity: 1 Approver : Jdoe7 Approver Full Name: John Doe PCSpecs: 18 GB Memory

Once parsed the data should look like this:

Region	Config Type	Business Case	MAC Address	Quantity	ApproverID	Approver Full Name	PCSpecs
North America	Windows 10 (WIN10)	Need Upgrade	1234567	1	Jdoe7	John Doe	18 GB memory

I have tried the Regex tool to make sense of this but I am having trouble. Can anyone offer any assistance? Thank you.

[danilang]

Hi @Fred22

Start by splitting to rows on line breaks.  Then split to columns around the colon ":".  Crosstab and clean up

Note:  the workflow assumes that the excel file is in the same directory

Dan

[Fred22]

Hi Dan,

Thank you so much this worked perfectly for this request. As we have gotten a few more requests the data is inserted differently for some.

Do you mind providing assistance on a case like this? The flow below thinks the additional Locations are fields.

Thank you very much.

[danilang · Accepted solution]

Hello @Fred22

This new solution takes care of the cases where any field is split between multiple rows.

Dan

[Fred22]

This is perfect. Thank you very much!