I want to split the record as below. Appreciate the support. Existing Record: RecordWODistrictLeadTechService1A00100, A00101North,southKhan, LopezAssess, Replace Desired Result: RecordWODistrictLeadTechService1A00100NorthKhanAssess2A00101southLopezReplace

Split the record based on the delimiter

I want to split the record as below. Appreciate the support.

Existing Record:

Record	WO	District	LeadTech	Service
1	A00100, A00101	North,south	Khan, Lopez	Assess, Replace

Desired Result:

Record	WO	District	LeadTech	Service
1	A00100	North	Khan	Assess
2	A00101	south	Lopez	Replace

Developer

Accepted answers

Kenda

Hello @jmedidi

If you update the District cell in the Text Input in the workflow attached previously with no updates to the other fields, you will see that the output now contains 4 records as follows:

All comments

Christina_H

This works on the data provided

Split record.yxmd

Kenda

Hi @jmedidi

In this case, it may help to transpose the data first. This will allow you to then use just one Text to Columns tool (splitting to rows). I added a Formula tool to just trim this field that we split so there is no extra whitespace. I then used a Multi-Row Formula tool to edit the Record column so that we could then use a Cross Tab to get the records back on their own rows.

Hope this helps!

New Workflow2.yxmd

jmedidi

Thanks for quick response.

Will this work if i have entries more than 2 in the cell for eg., north, south, east, west and other entries remain same ?

atcodedog05

Hi @jmedidi

The method should work for n number of splits also. You can try and check it out.

Kenda

Hello @jmedidi

If you update the District cell in the Text Input in the workflow attached previously with no updates to the other fields, you will see that the output now contains 4 records as follows:

jmedidi

Hello Kenda,

Actually i have added column Record for illustration purpose only. If i remove the column Record , i am getting an error "Type Mismatch in operator + " at Mutil row formula.

Appreciate your support.

Kenda

Hi @jmedidi

This is because the Multi-Row Formula tool is actually trying to update the Record field. If you want to get rid of it from your input, you could change the Multi-Row formula tool so that it creates a new field called Record instead (with the same formula as before). This field is necessary because the Cross Tab tool uses it to group the data and separate the rows correctly.

Hope this helps!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703