Regex to parse concatenated field

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.

TestData.xlsx

Workflow

Accepted answers

danilang

Hello @Fred22

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

Solution v1.1.png

Dan

Solution v1.1.yxmd

All comments

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

Solution.yxmd

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.

TestData.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335