Create new rows from dilimited data...

[paulb1]

I have a file and the Program/Fund field has program and fund info (ex. Outreach - 50), there are some rows that have multiple programs/funds, some have 2, some 3, and some 4.

I need a way to split the program/fund field and create new rows for the additional program/funds. I would want the new rows to retain the values of the original row it was split from. 

I hope this table is enough...

Gift Type	Campaign	Division	Program/Fund	Funder ID	Funder	Name	Status
Government Grant	Public Funding	Homeless Services	Outreach - 50	11959	City of Tester1	2019-20 Cities - SOP	Funded
Government Grant	Public Funding	Homeless Services	Safe Haven - 46; Parkland - 49	11959	City of Tester1	2019-20 Cities - Shelter	Funded
Government Grant	Public Funding	Homeless Services	NFGG - 63; NNFB - 60;  NFR - 68; NFOL - 73	11959	City of Tester1	2019-20  Cities - TLF	Funded
Government Grant	Public Funding	Homeless Services	New Found Grace Point - 70	12670	City of Tester2	FY 2019 (10/1/20 - 9/30/21) HUD CoC, NFGP	Pending
Government Grant	Public Funding	Homeless Services	New Found Grace Point - 70	12670	City of Tester2	CY 2019 General Funds - NFGP	Funded
Government Grant	Public Funding	Homeless Services	Supportive Housing - 72	12670	City of Tester2	CY 2019 General Funds - RRH	Funded
Government Grant	Public Funding	Homeless Services	New Found Grace Point - 70	12670	City of Tester2	FY 2017 (10/1/18 - 9/30/19) HUD, NFGP	Funded
Government Grant	Public Funding	Homeless Services	New Found Grace Point - 70	12670	City of Tester2	FY 2018 (10/1/19 - 9/30/20) HUD, NFGP	Funded
Government Grant	Public Funding	Homeless Services	Supportive Housing - 72	12670	City of Tester2	2017 HUD CoC, RRH (8/1/18 - 7/31/19)	Funded

Thank you,

Paul Brainard

[Thableaus]

Hi @paulb1 

Is this table your desired Output or your Input?

Could you please give more details about these new rows? How many rows would that be?

Cheers,

[nakamott · Accepted solution]

Is the delimiter always a semicolon?

If so you just need the Parse tool, text to columns.

Split to rows and set the delimiter to a semicolon

[paulb1]

Thanks for the reply. This is my input table. I only want one program/fund per row. For the programs/funds I split out I want to retain all of the other values of the split from row.


Hope this helps!!

[paulb1]

That looks simple enough, I'll give it a try tomorrow!  Yes, always a semi-colon.